求出MySQL中一个表内字段中相互关联的id值,如图片所示,1中有B,而3中也有B,所以选出1和3,但是3中有D,而5中也有D
所以选出1,3,5.就如图片中的描述一样,这个问题一直搞不懂,曾经将该字段逗号分割行列转换然后进行其他处理,能求出直接关联的数据(如1和3,3和5)但是还是一直卡在无法同时求出所有有间接关联的数据的id,即一直求不出1和5关联的id。
所以选出1,3,5.就如图片中的描述一样,这个问题一直搞不懂,曾经将该字段逗号分割行列转换然后进行其他处理,能求出直接关联的数据(如1和3,3和5)但是还是一直卡在无法同时求出所有有间接关联的数据的id,即一直求不出1和5关联的id。
参考一下这个贴子的提问方式http://bbs.csdn.net/topics/320211382
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
table及insert代码:
create table test(
`id` int unsigned primary key auto_increment,
`users` varchar(20)
)engine=innodb,charset = utf8;insert into test(`users`)
values('A,B'),('C'),('B,D'),('E'),('D'),('F,G'),('G'),('C');
结果大致应该为:
-- +----+------+
-- | id | cate |
-- +----+------+
-- | 1 |1,3,5 |
-- | 2 |2,8 |
-- | 3 | 4 |
-- | 4 | 6,7 |主要是将其关联数据的id归位一个字符串作为一个字段的值;
我考虑过用存储过程,但是比较伤脑筋。谢谢大家的帮忙。
我写了一个存储过程,结果和代码如下:
drop procedure if exists tmp1;
-- record_tb为表名
-- rt_id为主键名
-- users为存储关联信息的目标字段名
-- sep_num为一行users中的最大逗号数量
create procedure tmp1(IN record_tb varchar(20),IN rt_id varchar(20),IN users varchar(20),IN sep_num smallint unsigned)
BEGIN
declare s int unsigned default 1;
-- 生成临时表cate:处理逗号分割字段行列转换后进行去重的场所
create temporary table if not exists cate(
`id` int unsigned primary key auto_increment,
`c_id` int unsigned,
`cate` varchar(40)
);
truncate table cate;
-- 用来记录最终所有互相关联数据,
create temporary table if not exists list(
`id` int unsigned primary key auto_increment,
`c_ids` varchar(40),
`list` varchar(40)
);
truncate table list;
-- 生成辅助表,用来逗号分割行列转换
create temporary table if not exists incre_table(
`AutoIncreID` smallint unsigned primary key auto_increment
);
truncate table incre_table;
-- 初始化辅助表(incre_table)----------------------- WHILE s <= sep_num DO
BEGIN
insert into incre_table select s;
set s = s+1;
END;
END WHILE;
-- 声明初始化变量
-- @id用来匹配record_tb表中的id
-- @str用来统计每组各关联数据,关联数据之间用逗号分割
-- @用来记录已经处理过的关联数据
set @id=1;
set @str ='1';
set @ = '';
loop1: LOOP
-- 将record_tb表中符合id值的数据插入到临时表cate中
insert into cate(`c_id`,`cate`) select a.rt_id,a.users from record_tb a where a.rt_id=@id;
-- 进行逗号分割行列转换后将逗号分割字符串形成多个单字符元素列,然后选出record_tb的users中除本次循环所用的id值外含有cate中元素的id值,赋给@id
-- 判断record中user字段是否存在含有该组元素的字符串,
-- 如果有,将该组中所关联的ID值组成字符串@str,继续将相关ID的字符串插入cate表进行行列转换
-- 如果没有,说明该组关联数据统计完毕,将@str插入list表的c_ids中,
-- 将cate表中的单个元素列(cate字段)进行去重,组成字符串后插入list表中的list字段中,
select c.rt_id into @id from record_tb c inner join (
select a.c_id, substring_index(substring_index(a.cate,',',b.AutoIncreID),',',-1) as result from cate a
join incre_table b on b.AutoIncreID<=(length(a.cate)-length(replace(a.cate,',',''))+1)
) as d on locate(d.result,c.users) and c.rt_id<>d.c_id where not locate(c.rt_id,@str);
if row_count()>0 then
select concat(@id,",",@str) into @str;
else
insert into list(`c_ids`,`list`) select @str,group_concat(c.result) as list from(
select distinct substring_index(substring_index(a.cate,',',b.AutoIncreID),',',-1) as result from cate a
join incre_table b on b.AutoIncreID<=(length(a.cate)-length(replace(a.cate,',',''))+1)
) c;
-- @记录被统计过的数据id,当一组关联数据插入到list表后,继续在record_tb表中搜索没有被统计的数据id
-- 直到record_tb中所有值都被统计过为止
select concat(@str,",",@) into @;
select a.rt_id into @id from record_tb a where not locate(a.rt_id,@) limit 1;
if row_count()<=0 THEN
LEAVE loop1;
END IF;
select @id into @str;
-- 清空cate表,准备下一组的关联数据进入
truncate table cate;
end if;
END LOOP loop1;
select * from list;
END;
//
不过太慢了,不知道怎么优化才好。请大家共同分析一下。谢谢大家了!
`rt_id` int unsigned primary key auto_increment,
`users` varchar(20)
)engine=innodb,charset = utf8;insert into record_tb(`users`)
values('A,B'),('C'),('B,D'),('E'),('D'),('F,G'),('G'),('C');