sql 怎么查找两个商品共同出现的次数

create table #database(dingdan varchar(10),shangpin varchar(1))insert into #database values('a10','a')insert into #database values('a9','a')insert into #database values('a9','b')insert into #database values('a8','b')insert into #database values('a8','c')insert into #database values('a8','d')insert into #database values('a7','b')insert into #database values('a7','d')insert into #database values('a6','a')insert into #database values('a6','c')insert into #database values('a5','a')insert into #database values('a5','d')select * from #database

USE testGO--IF object_id('tempdb..#database')IS NOT NULL--    DROP TABLE #database--gocreate table #database(dingdan varchar(10),shangpin varchar(1)) insert into #database values('a10','a') insert into #database values('a9','a') insert into #database values('a9','b') insert into #database values('a8','b') insert into #database values('a8','c') insert into #database values('a8','d') insert into #database values('a7','b') insert into #database values('a7','d') insert into #database values('a6','a') insert into #database values('a6','c') insert into #database values('a5','a') insert into #database values('a5','d') go/*第一步，需要查询出b,d 2a,b 1a,c 1a,d 1c,d 1b,c 1.第二步，也可同时查询出 三件商品的出现次数b,c,d 1*/-- 1.SELECT         a.shangpin+','+b.shangpin    AS Result        ,COUNT(1)                    AS Qty    FROM #database AS a        INNER JOIN #database AS b ON a.dingdan=b.dingdan AND UNICODE(a.shangpin)<UNICODE(b.shangpin)    GROUP BY a.shangpin+','+b.shangpin-- 2.SELECT         a.shangpin+','+b.shangpin+','+c.shangpin    AS Result        ,COUNT(1)                                    AS Qty    FROM #database AS a        INNER JOIN #database AS b ON a.dingdan=b.dingdan AND UNICODE(a.shangpin)<UNICODE(b.shangpin)        INNER JOIN #database AS c ON a.dingdan=c.dingdan AND UNICODE(b.shangpin)<UNICODE(c.shangpin)    GROUP BY a.shangpin+','+b.shangpin+','+c.shangpin    GO--IF object_id('tempdb..#database')IS NOT NULL--    DROP TABLE #database--go