left join a on b like而不是=

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'

 with t1 as (
  select 'ab123' a from dual union all
  select 'ac456' a from dual union all
  select 'bc789' a from dual 
  ),
  t2 as(
  select 'ab456' b from dual union all
  select 'ac789' b from dual union all
  select 'bc123' b from dual
  )
  select t1.*,t2.* from t1 left join t2 ON (b like substr(a,1,2)||'%')