sql server 2000 根据销售结果怎么获取，商品最搭配的销售组合？
sql server 2000 根据销售结果如何获取，商品最搭配的销售组合？？？
------解决思路----------------------
------解决思路----------------------
拿到所有组合，针对销售数量分组求和 ，降序排序。只考虑了组合情况，单卖的再想办法。
------解决思路----------------------
------解决思路----------------------
销售明细数据如下: 如何求出最佳销售搭配为(A + B)或者能求出每个销售组合出现的具体次数，如A+B 3次 (B+C)和(A+C) 出现2次
A + B
A + C
B + C
A + B + C
A + B + D
Create table #sale_master(
sheet_no varchar(40) not null primary key,
sheet_date datetime )
Create table #sale_detail(
sheet_no varchar(40) not null,
line_no int not null,
item_no varchar(40) not null,
qty decimal(16,6) not null
primary key (sheet_no,line_no) )
insert into #sale_master values('1',GETDATE())
insert into #sale_detail values ('1',1,'A',1)
insert into #sale_detail values ('1',2,'B',1)
insert into #sale_master values('2',GETDATE())
insert into #sale_detail values ('2',1,'A',1)
insert into #sale_detail values ('2',2,'C',1)
insert into #sale_master values('3',GETDATE())
insert into #sale_detail values ('3',1,'B',1)
insert into #sale_detail values ('3',2,'C',1)
insert into #sale_master values('4',GETDATE())
insert into #sale_detail values ('4',1,'A',1)
insert into #sale_detail values ('4',2,'B',1)
insert into #sale_detail values ('4',3,'C',1)
insert into #sale_master values('5',GETDATE())
insert into #sale_detail values ('5',1,'A',1)
insert into #sale_detail values ('5',2,'B',1)
insert into #sale_detail values ('5',3,'D',1)
------解决思路----------------------
--最佳销售搭配
SELECT TOP 1 T1.item_no+'+'+T2.item_no AS[最佳销售搭配]
FROM #sale_detail T1
JOIN #sale_detail T2 ON T1.sheet_no=T2.sheet_no AND T1.item_no<T2.item_no
GROUP BY T1.item_no+'+'+T2.item_no
ORDER BY COUNT(1)DESC
--求出每个销售组合出现的具体次数
SELECT T1.item_no+'+'+T2.item_no AS[销售组合]
,COUNT(1)AS[具体次数]
FROM #sale_detail T1
JOIN #sale_detail T2 ON T1.sheet_no=T2.sheet_no AND T1.item_no<T2.item_no
GROUP BY T1.item_no+'+'+T2.item_no
------解决思路----------------------
拿到所有组合，针对销售数量分组求和 ，降序排序。只考虑了组合情况，单卖的再想办法。
with cte as
(
select cast(item_no as varchar) item_no,line_no,sheet_no,cast(qty as decimal(16,6)) qty,1 level from #sale_detail
union all
select cast(a.item_no + '+' + b.item_no as varchar),b.line_no,a.sheet_no,cast(a.qty+b.qty as decimal(16,6)),level + 1 from cte a
join #sale_detail b on a.sheet_no = b.sheet_no and a.line_no < b.line_no
)
select item_no 销售组合,sum(qty) 销售数量,COUNT(*) 组合出现次数 from cte
where level <> 1
group by item_no
order by sum(qty) desc,item_no
------解决思路----------------------
-- 先把想要统计的组合建起来
Create table #check_list(
group_no int not null,
title varchar(200)
)
Create table #check_detail(
group_no int not null,
item_no varchar(40) not null
)
INSERT INTO #check_detail VALUES(1,'A')
INSERT INTO #check_detail VALUES(1,'B')
INSERT INTO #check_detail VALUES(2,'A')
INSERT INTO #check_detail VALUES(2,'C')
INSERT INTO #check_detail VALUES(3,'B')
INSERT INTO #check_detail VALUES(3,'C')
INSERT INTO #check_detail VALUES(4,'A')
INSERT INTO #check_detail VALUES(4,'B')
INSERT INTO #check_detail VALUES(4,'C')
INSERT INTO #check_detail VALUES(5,'A')
INSERT INTO #check_detail VALUES(5,'B')
INSERT INTO #check_detail VALUES(5,'D')
INSERT INTO #check_list
SELECT group_no,
Stuff((SELECT '+'+item_no
FROM #check_detail cd
WHERE cd.group_no = cl.group_no
FOR XML PATH('')
),
1,1,'') title
FROM (SELECT DISTINCT group_no FROM #check_detail) cl
GO
--SELECT * FROM #check_list
--SELECT * FROM #check_detail
SELECT cl.title,
COUNT(s.sheet_no) c
FROM #check_list cl
JOIN (
SELECT c.group_no,
c.sheet_no
FROM (
SELECT cd.group_no,
sm.sheet_no,
cd.item_no
FROM #check_detail cd,
#sale_master sm
) c
LEFT JOIN #sale_detail sd
ON c.sheet_no = sd.sheet_no
AND c.item_no = sd.item_no
GROUP BY c.group_no,c.sheet_no
HAVING COUNT(DISTINCT c.item_no) = COUNT(DISTINCT sd.item_no)
) s
ON cl.group_no = s.group_no
GROUP BY cl.title
title c
-------- -----------
A+B 3
A+B+C 1
A+B+D 1
A+C 2
B+C 2
------解决思路----------------------
--销售明细数据如下: 如何求出最佳销售搭配为(A + B)或者能求出每个销售组合出现的具体次数，如A+B 3次 (B+C)和(A+C) 出现2次
--A + B
--A + C
--B + C
--A + B + C
--A + B + D
if OBJECT_ID('test','U') > 0 drop table test
if OBJECT_ID('tempdb..#sale_master','U') > 0 drop table #sale_master
if OBJECT_ID('tempdb..#sale_detail','U') > 0 drop table #sale_detail
Create table #sale_master(
sheet_no varchar(40) not null primary key,
sheet_date datetime )
Create table #sale_detail(
sheet_no varchar(40) not null,
line_no int not null,
item_no varchar(40) not null,
qty decimal(16,6) not null
primary key (sheet_no,line_no) )
insert into #sale_master values('1',GETDATE())
insert into #sale_detail values ('1',1,'A',1)
insert into #sale_detail values ('1',2,'B',1)
insert into #sale_master values('2',GETDATE())
insert into #sale_detail values ('2',1,'A',1)
insert into #sale_detail values ('2',2,'C',1)
insert into #sale_master values('3',GETDATE())
insert into #sale_detail values ('3',1,'B',1)
insert into #sale_detail values ('3',2,'C',1)
insert into #sale_master values('4',GETDATE())
insert into #sale_detail values ('4',1,'A',1)
insert into #sale_detail values ('4',2,'B',1)
insert into #sale_detail values ('4',3,'C',1)
insert into #sale_master values('5',GETDATE())
insert into #sale_detail values ('5',1,'A',1)
insert into #sale_detail values ('5',2,'B',1)
insert into #sale_detail values ('5',3,'D',1)
DECLARE @STR VARCHAR(8000) = ''
SELECT @STR = @STR +'
,MAX(case when item_no = '''+item_no + ''' then item_no end)'+ item_no FROM #sale_detail
group by item_no
print @str
exec('select a.sheet_no'+@str+' into test from #sale_detail a
group by a.sheet_no')
set @STR = ''
select @STR = @STR + 'isnull('+name+','''') + ' from syscolumns where object_id('test','U') = id and name <>'sheet_no'
set @STR = LEFT(@str,len(@str) -2)
print @str
exec('select sheet_no,' +@STR +'zuhe from test')
/*
sheet_no zuhe
1 AB
2 AC
3 BC
4 ABC
5 ABD
/*