sql server 2000 根据销售结果如何获取,商品最搭配的销售组合???

PB菜鸟 2015-04-21 12:14:20
 
销售明细数据如下: 如何求出最佳销售搭配为(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)
...全文
139 点赞 收藏 9
写回复
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
--最佳销售搭配
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
回复
PB菜鸟 2015-04-21
引用 1 楼 frankl123 的回复:
没懂什么意思,你能解释下字段吗
就是一个简单的销售关系表 #sale_master 销售主表 sheet_no 销售单号 ,sheet_date 销售日期 #sale_detal 销售明细表 sheet_no 销售单号,line_no 行号 ,item_no 商品编号, qty 销售数量 根据已有的销售数据,想统计某段时间内顾客买了X商品同时又买了Y商品的次数,就是销售业务中的连带销售,统计顾客的购物习惯,方便卖家做商品摆放及促销活动。
回复
frankl123 2015-04-21
没懂什么意思,你能解释下字段吗
回复
hilex_jay 2015-04-21

 
 
--销售明细数据如下: 如何求出最佳销售搭配为(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
/*








回复
Tiger_Zhao 2015-04-21
那么 #check_list 自己逐条写INSERT,我只不过是想方便点,不影响功能。
回复
hilex_jay 2015-04-21
2000的数据库啊,各位大神`~
回复
frankl123 2015-04-21
我刚想了想ABC也不能简单的抽象成A+C B+C A+B+C的组合,还是要再仔细想想
回复
Tiger_Zhao 2015-04-21
-- 先把想要统计的组合建起来
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
回复
frankl123 2015-04-21
拿到所有组合,针对销售数量分组求和 ,降序排序。只考虑了组合情况,单卖的再想办法。
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 
回复
发动态
发帖子
community_281
创建于2021-05-27

92

社区成员

申请成为版主
社区公告
暂无公告