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 test
GO


--IF object_id('tempdb..#database')IS NOT NULL
--    DROP TABLE #database
--go
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') 
go

/*
第一步，需要查询出
b,d 2
a,b 1
a,c 1
a,d 1
c,d 1
b,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