sql 如何查找两个商品共同出现的次数
如何查询用户订单中，商品各中商品同时出现的次数
- SQL code
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第一步，需要查询出
b,d 2
a,b 1
a,c 1
a,d 1
c,d 1
b,c 1.
第二步，也可同时查询出 三件商品的出现次数
b,c,d 1
[解决办法]
- SQL code
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