SqlServer LEFT JOIN 不用on 用LIKE的问题

Question

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'

不过行不通，望大牛们指点

Answer 1

用标量子查询就可以的，我平时使用的是mysql

select cat_id,(SELECT COUNT(1) FROM A WHERE FIND_IN_SET(b.cat_id,cat_ids)>0) AS NUM
from B b

Answer 2

;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