table A 商品表
id cat_ids
1 1,2,3
2 1,2
3 1
table B 分类表
cat_id
1
2
3
表结构如上 如要搜索出 b表每个分类 含有多少个商品,应如何编写sql
本人想到的是
select count(*) as goods_num from b left join a on b.cat_id like 'a.cat_ids'
不过行不通,望大牛们指点
用标量子查询就可以的,我平时使用的是mysql
select cat_id,(SELECT COUNT(1) FROM A WHERE FIND_IN_SET(b.cat_id,cat_ids)>0) AS NUM
from B b
;WITH a(id,cat_ids) AS (
select 1,'1,2,3' UNION ALL
select 2,'1,2' UNION ALL
select 3,'1'
),b AS (
SELECT * FROM ( VALUES(1),(2),(3)) b(cat_id)
)
SELECT b.cat_id,COUNT(0) AS num
FROM b LEFT JOIN a ON CHARINDEX(','+CONVERT(VARCHAR,b.cat_id)+',',','+a.cat_ids+',')>0
GROUP BY b.cat_id
cat_id num
1 3
2 2
3 1