求每个店铺每件商品的销售额的前5
实际要求比这个还要复杂，我简略的描述一下。
门店|商品|数量|销售额
A001,C001,1,1
A001,C002,2,2
A001,C003,3,3
A002,C001,1,1
A002,C003,3,3
A004,C004,4,4
A004,C002,2,2
求每个门店销售额前5的商品，数量总和，销售额总和
结果最好为
门店1|商品TOP1|TOP1总量|TOP1销售总额 ..... |商品TOP5|TOP5总量|TOP5销售总额
门店2|商品TOP1|TOP1总量|TOP1销售总额 ..... |商品TOP5|TOP5总量|TOP5销售总额
结果实在变换太麻烦的话，竖排也行
门店1|商品TOP1|TOP1总量|TOP1销售总额
门店1|商品TOP2|TOP2总量|TOP2销售总额
求每家店铺，每种商品的两次分组计算的SQL文。
注：不要求必须一行实现
[解决办法]
适合2005及以上的版本：
--drop table tb
create table tb(门店 varchar(10),商品 varchar(10),数量 int,销售额 numeric(10,2))
insert into tb
select 'A001','C001',1,1 union all
select 'A001','C002',2,2 union all
select 'A001','C003',3,3 union all
select 'A002','C001',1,1 union all
select 'A002','C003',3,3 union all
select 'A004','C004',4,4 union all
select 'A004','C002',2,2
go
;with t1
as
(
select *,
ROW_NUMBER() over(PARTITION by 门店 order by 销售额 desc) rownum
from tb
),
t2
as
(
select *,
门店+'
[解决办法]
'+商品+'
[解决办法]
'+cast(数量 as varchar)+'
[解决办法]
'+cast(销售额 as varchar) as v
from t1
where rownum <= 5
)
select distinct
STUFF((select '
[解决办法]
'+v from t2 b where a.门店 = b.门店 for xml path('')),
1,1,'') as v
from t2 a
/*
v
A001
[解决办法]
C003
[解决办法]
3
[解决办法]
3.00
[解决办法]
A001
[解决办法]
C002
[解决办法]
2
[解决办法]
2.00
[解决办法]
A001
[解决办法]
C001
[解决办法]
1
[解决办法]
1.00
A002
[解决办法]
C003
[解决办法]
3
[解决办法]
3.00
[解决办法]
A002
[解决办法]
C001
[解决办法]
1
[解决办法]
1.00
A004
[解决办法]
C004
[解决办法]
4
[解决办法]
4.00
[解决办法]
A004
[解决办法]
C002
[解决办法]
2
[解决办法]
2.00
*/
if object_id('tempdb..#a') is not null drop table #a
if object_id('tempdb..#b') is not null drop table #b
go
create table #a(门店 varchar(10),商品 varchar(10),数量 int,销售额 numeric(10,2))
insert into #a
select 'A001','C001',1,1 union all
select 'A001','C002',2,2 union all
select 'A001','C002',2,11 union all
select 'A001','C003',3,3 union all
select 'A002','C001',1,1 union all
select 'A002','C003',3,3 union all
select 'A002','C004',2,1 union all
select 'A002','C004',2,1 union all
select 'A002','C007',2,1 union all
select 'A002','C002',2,1 union all
select 'A002','C005',3,12 union all
select 'A002','C006',3,6 union all
select 'A002','C003',3,3 union all
select 'A004','C004',4,4 union all
select 'A004','C002',2,2
-- 查询开始
;with cte as(
select * ,sum([数量]) over ( partition by [门店],[商品])'数量总和',
sum([销售额]) over ( partition by [门店],[商品])'销售额总和' from #a
)
select [门店],[商品],[数量总和],[销售额总和] ,dranum as '排名' into #b from (
select *,row_number() over (partition by [门店],[商品] order by [销售额总和] desc ) rownum ,
dense_rank() over (partition by [门店] order by [销售额总和] desc,[商品]) dranum from cte ) a
where rownum=1 and dranum<=5
--竖排的查询
--select * from #b
--横排的查询
declare @sql varchar(1000) , @count int
select @sql=isnull(@sql+',','')+'max(case when [排名]='''+Convert(varchar(10),[排名])+''' then [销售额总和] else 0 end ) as TOP'+Convert(varchar(10),[排名])+'销售额 ,
max(case when [排名]='''+Convert(varchar(10),[排名])+''' then [数量总和] else 0 end ) as TOP'+Convert(varchar(10),[排名])+'总量 '
from (select distinct [排名] from #b) a
exec('select [门店],'+@sql+ 'from #b group by [门店]')
/*
门店 TOP1销售额 TOP1总量 TOP2销售额 TOP2总量 TOP3销售额 TOP3总量 TOP4销售额 TOP4总量 TOP5销售额 TOP5总量
---------- --------------------------------------- ----------- --------------------------------------- ----------- --------------------------------------- ----------- --------------------------------------- ----------- --------------------------------------- -----------
A001 13.00 4 3.00 3 1.00 1 0.00 0 0.00 0
A002 12.00 3 6.00 6 6.00 3 2.00 4 1.00 1
A004 4.00 4 2.00 2 0.00 0 0.00 0 0.00 0
(3 行受影响)
*/