22,206
社区成员
发帖
与我相关
我的任务
分享
drop table if exists #list
select dates=cast('2018-9-1' as datetime),docno='101',goodcode='A01',qty=2 into #list union all
select dates=cast('2018-9-1' as datetime),docno='101',goodcode='A02',qty=1 union all
select dates=cast('2018-9-2' as datetime),docno='102',goodcode='A01',qty=1 union all
select dates=cast('2018-9-2' as datetime),docno='102',goodcode='A02',qty=2 union all
select dates=cast('2018-9-2' as datetime),docno='102',goodcode='A03',qty=1 union all
select dates=cast('2018-9-3' as datetime),docno='103',goodcode='A01',qty=1 union all
select dates=cast('2018-9-3' as datetime),docno='103',goodcode='A02',qty=2 union all
select dates=cast('2018-9-3' as datetime),docno='103',goodcode='A04',qty=1 union all
select dates=cast('2018-9-3' as datetime),docno='103',goodcode='A05',qty=3 union all
select dates=cast('2018-9-3' as datetime),docno='104',goodcode='A02',qty=2
declare @sumdoc int=(select count(distinct docno) from #list)
;
with list as(
select goodcode,doccount=count(distinct docno)
from #list
group by goodcode
),goods as (
select docno,goodslist=(select goodcode+',' from #list a where a.docno=#list.docno for xml PATH(''))
from #list
group by docno
)
select a.goodcode,b.goodcode,a.doccount,b.doccount,'a+b'=(select count(*) from goods where charindex(a.goodcode,','+goodslist)>0 and charindex(b.goodcode,','+goodslist)>0)
,sumdoc=@sumdoc
from list a
inner join list b on a.goodcode<b.goodcode