统计字段出现次数

码拜

9年 ago

假设本人有两个表
table1
id value
1 张三,李四
2 张三，王五
3 张三马六
table2
id name
1 张三
2 李四
3 王五
4 马六
现在本人想统计table2中全部的人在table1中出现的次数，结果应该是这样的
张三 3
李四 1
王五 1
马六 1
求高手指点

解决方案

也是新手，感觉四次挺麻烦的，期待更好的方法以供学习。
select name,count from (SELECT count(*) as count from table1 a where length(value)-length(replace(value,(SELECT name from table2 where id = 1),””))) a
CROSS JOIN
(SELECT name from table2 where id = 1) b
UNION
select name,count from (SELECT count(*) as count from table1 a where length(value)-length(replace(value,(SELECT name from table2 where id = 2),””))) a
CROSS JOIN
(SELECT name from table2 where id = 2) b
UNION
select name,count from (SELECT count(*) as count from table1 a where length(value)-length(replace(value,(SELECT name from table2 where id = 3),””))) a
CROSS JOIN
(SELECT name from table2 where id = 3) b
UNION
select name,count from (SELECT count(*) as count from table1 a where length(value)-length(replace(value,(SELECT name from table2 where id = 3),””))) a
CROSS JOIN
(SELECT name from table2 where id = 4) b

select table2.name,count(*)
from table2 , table1
where find_in_set(table2.name,table1.value)

select table2.name,count(*)
from table2 , table1
where find_in_set(table2.name,table1.value)
group by table2.name