mysql 找出啤酒与尿布的商品

啤酒与尿布具体典故请自己百度

简单说来，顾客去商场买东西，商场想找哪两个商品组合卖出的最高，或者说顾客最喜欢把哪两个商品放在一起买

得到这样的数据，可利于商场陈列商品

如下语句就能找出两两组合卖得最多的商品组合。

billid 代表小票编号 相同则是同一张小票上的商品

with cte1 as
( 
  select 1 as billid,'瓜子' as goodsname,30 as qty union all
  select 1 as billid,'花生' as goodsname,10 as qty union all
  select 1 as billid,'矿泉水' as goodsname,30 as qty union all 
  
  select 2 as billid,'瓜子' as goodsname,30 as qty union all
  select 2 as billid,'花生' as goodsname,10 as qty union all

  select 3 as billid,'花生' as goodsname,10 as qty union all
  select 3 as billid,'矿泉水' as goodsname,30 as qty union all   
  
  select 4 as billid,'瓜子' as goodsname,30 as qty union all
  select 4 as billid,'矿泉水' as goodsname,30 as qty union all 
  
  select 5 as billid,'瓜子' as goodsname,30 as qty union all
  select 5 as billid,'矿泉水' as goodsname,30 as qty 
  
),
  tmp1 as
 (
 select distinct billid,goodsname from  cte1)
 ,
 tmp2 as
 (
 select t1.billid,t1.goodsname as goodsname1,t2.goodsname  as goodsname2 from tmp1 as t1,tmp1 as t2  
 where t1.billid = t2.billid 
 and  t1.goodsname <> t2.goodsname and t2.goodsname >t1.goodsname
 order by t1.billid,t1.goodsname,t2.goodsname 
)
select count(*) as cnt , goodsname1,goodsname2 from tmp2
group by goodsname1,goodsname2
order by cnt desc 

3 瓜子 矿泉水
2 瓜子 花生
2 矿泉水 花生

可以看到 瓜子 矿泉水 这两个组合出现的次数最多