求一个SQL语句， 关于成组出现最多的~~
我的订单明细表：
BillID(订单编号) code(商品编号) qty(数量)
1 a 1
1 b 2
1 c 1
2 a 2
2 d 1
3 a 2
3 b 1
3 d 2
4 d 1
5 a 2
5 d 1
求一个SQL语句， 统计出所有订单明细表里搭配销售出现最多的商品并排序（订单明细里的商品必须有2个），
结果表：
code1(商品编号) code2(商品编号) cnt(搭配次数) qty(总销售数量)
a d 2 8
a b 2 5
这是测试数据：
if object_id('tempdb..#xs') is not null
drop table #xs;
create table #xs(billid int, code varchar(20), qty int)
insert into #xs(billid , code , qty )
select 1, 'a',1 union all
select 1, 'b',2 union all
select 1, 'c',1 union all
select 2, 'a',2 union all
select 2, 'd',1 union all
select 3, 'a',2 union all
select 3, 'b',1 union all
select 3, 'd',2 union all
select 4, 'd',1 union all
select 5, 'a',2 union all
select 5, 'd',1
[解决办法]
with sel as( select *,row_number() over( partition by billid order by code) as rn from #xs)
,sel2 as(
select a.code code1,b.code code2,a.qty+b.qty qty from sel a join sel b on a.billid=b.billid and a.rn< b.rn)
select code1,code2,count(1) cnt,sum(qty) from sel2 group by code1,code2
create table #xs(billid int, code varchar(20), qty int)
insert into #xs(billid , code , qty )
select 1, 'a',1 union all
select 1, 'b',2 union all
select 1, 'c',1 union all
select 2, 'a',2 union all
select 2, 'd',1 union all
select 3, 'a',2 union all
select 3, 'b',1 union all
select 3, 'd',2 union all
select 4, 'd',1 union all
select 5, 'a',2 union all
select 5, 'd',1
select code,code1,count(distinct billid),sum(qty) qty
from (select distinct a.code,b.code code1,c.billid,c.qty,c.code code2
from (select a.billid billid ,b.billid billid1,a.code,a.qty
from #xs a join #xs b on a.billid<b.billid and a.code=b.code) a
join (select a.billid billid ,b.billid billid1,a.code,a.qty
from #xs a join #xs b on a.billid<b.billid and a.code=b.code ) b
on a.billid=b.billid and a.billid1=b.billid1 and a.code<b.code
join #xs c on ((a.billid=c.billid and a.code=c.code) or(b.billid1=c.billid and b.code=c.code)
or(a.billid=c.billid and b.code=c.code)or(b.billid1=c.billid and a.code=c.code))) aa
group by code,code1 order by qty desc
drop table #xs
;with tab as (结果：
select code1,code2,COUNT(*) cnt,SUM(qty) qty from
(select a.code code1,b.code code2,a.qty+b.qty qty from #xs a
join #xs b on a.billid=b.billid and a.code<b.code
) t group by code1,code2 )
select * from tab where cnt=(select MAX(cnt) from tab)
