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)||'%')

阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/ethan_10/article/details/80322346
个人分类: oracle
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭