求一个SQL的算法

Question

有如下数据,实际数据为上万条
门店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

Answer 1

--第一个
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

Answer 2

--SQL2000方法
--门店ID,商品ID,库存数量,自增ID
CREATE TABLE #cu1 (MD_ID INT,SP_ID INT,END_QTY INT,ID INT IDENTITY(1,1))
CREATE TABLE #cu2 (MD_ID INT,SP_ID INT,END_QTY INT)
CREATE TABLE #cu3 (MD_ID VARCHAR(100),SP_ID INT,END_QTY INT,ID INT IDENTITY(1,1))INSERT INTO #CU1(MD_ID,SP_ID,END_QTY)
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,          4insert INTO #cu2
SELECT MD_ID,SP_ID,END_QTY FROM #cu1 a 
WHERE id IN (SELECT TOP 3 id FROM #cu1 WHERE sp_id=a.sp_id ORDER BY end_qty DESC)
ORDER BY sp_id,md_idinsert INTO #cu3 (SP_ID,END_QTY)
SELECT SP_ID,SUM(END_QTY) FROM #cu2 GROUP BY SP_IDDECLARE @md_id VARCHAR(100),@i1 INT,@i2 INT
SELECT @i1=1,@i2=MAX(id) FROM #cu3
WHILE @i1<=@i2
BEGIN
SET @md_id=''
SELECT @md_id=@md_id+CASE WHEN @md_id='' THEN '' ELSE ',' end+RTRIM(md_id) 
FROM #cu2 WHERE sp_id=(SELECT sp_id FROM #cu3 WHERE id=@i1)
UPDATE #cu3 SET md_id=@md_id WHERE id=@i1
SET @i1=@i1+1
END--結果一
SELECT * FROM #cu2
--結果二
SELECT * FROM #cu3