求一个SQL的算法
有如下数据,实际数据为上万条
门店ID 商品ID 库存数量
1 11 10
2 11 20
3 11 30
4 11 40
5 11 50
1 12 100
2 12 100
3 12 20
4 12 30
1 13 40
2 13 4
3 13 50
1 14 40
2 14 4
数据库为sql server2000或2008
希望得到,每个商品库存数量最大的三个门店以内的数据
结果1:
门店ID 商品ID 库存数量
3 11 30
4 11 40
5 11 50
1 12 100
2 12 100
4 12 30
1 13 40
2 13 4
3 13 50
1 14 40
2 14 4
还有一种结果是,在结果1的基础,将同一商品多个门店ID转换为一行显示,并合计库存数量
如结果2:
门店ID 商品ID 库存数量
3 4 5 11 120
1 2 4 12 230
1 2 3 13 94
1 2 14 44
[解决办法]
--第一个
with tb(a,b,c)as(
select 1,11,10 union all
select 2,11,20 union all
select 3,11,30 union all
select 4,11,40 union all
select 5,11,50 union all
select 1,12,100 union all
select 2,12,100 union all
select 3,12,20 union all
select 4,12,30 union all
select 1,13,40 union all
select 2,13,4 union all
select 3,13,50 union all
select 1,14,40 union all
select 2,14,4 )
,tc as (select *,num=ROW_NUMBER() over(partition by b order by c desc) from tb)
select a,b,c from tc
where num<=3
order by b,a
--第二个
with tb(a,b,c)as(
select 1,11,10 union all
select 2,11,20 union all
select 3,11,30 union all
select 4,11,40 union all
select 5,11,50 union all
select 1,12,100 union all
select 2,12,100 union all
select 3,12,20 union all
select 4,12,30 union all
select 1,13,40 union all
select 2,13,4 union all
select 3,13,50 union all
select 1,14,40 union all
select 2,14,4 )
,tc as (select *,num=ROW_NUMBER() over(partition by b order by c desc) from tb)
select (select ' '+convert(varchar,a) from tc
where a.b=b and num<=3 order by a for xml path('')),b,sum(c) from tc a
where num<=3
group by b
