1.a、b两张表,a.id在b.ids是这样存储的,是用逗号分隔的,现在要统计出每个a.id在b表的次数。
2.如果在设计之初,考虑全面的话,是会有一张中间表的设计,但往往现实是残酷的,现在是维护迭代阶段,不让加中间表了。所以根据这个思路,就需要我们在查的时候建立出一张中间表。首先把b.ids展开,让它每个字段只有一条数据,于是有:
1.首先根据查出我们需要的数据
select id as pk, ids as col from b;
2.让它每个字段只有一条数据
select
aa.pk,
substring_index(substring_index(aa.col,',',bb.help_topic_id+1),',',-1) aID
from
( select id as pk, ids as col from b ) as aa
join
mysql.help_topic as bb
on bb.help_topic_id < (char_length(aa.col) - char_length(replace(aa.col,',',''))+1);
3.分组统计
SELECT aID, COUNT(aID) FROM
(
select
aa.pk,
substring_index(substring_index(aa.col,',',bb.help_topic_id+1),',',-1) aID
from
( select id as pk, ids as col from b ) as aa
join
mysql.help_topic as bb
on bb.help_topic_id < (char_length(aa.col) - char_length(replace(aa.col,',',''))+1)
) t0 GROUP BY aID;
4.再关联a表,这里就不写了,经过上面几步,已经可以与a.id一一对应了。
3.总结
难点就在列转行,建立中间表,让它每个字段只有一条数据