[在线等待救星]（一个查询困惑一星期了，有点难度的题，有请各路英雄来相助，小弟先谢过） 够长吧 呵 Help!!!

Question

研究了一个星期了，没做的出来，希望路过这里的大侠想想办法，可能讲不清楚，最好提供一下实现的SQL代码，  谢谢！
要求如下：      从SQL中自带的Northwind数据库的Products表中
      找出各种产品类别(CatagoryID)中库存数量(UnitsInStock)在前3位的
      产品的代号（ProductID）和名称(ProductName)。
                       (提示，需要使用临时表和游标)
唉  小弟困惑了一星期   ....     万望相助   3Q!     有请大侠！

Answer 1

select m.ProductID,ProductName from Products m,
(select top 3 CategoryID , UnitsInStock = sum(UnitsInStock) from Products group by CategoryID order by UnitsInStock desc) n
where m.CategoryID = n.CategoryID
order by m.CategoryID/*
ProductID   ProductName                              
----------- ---------------------------------------- 
1           Chai
2           Chang
24          Guaraná Fantástica
34          Sasquatch Ale
35          Steeleye Stout
38          Côte de Blaye
39          Chartreuse verte
43          Ipoh Coffee
67          Laughing Lumberjack Lager
70          Outback Lager
75          Rhönbräu Klosterbier
76          Lakkalikööri
3           Aniseed Syrup
4           Chef Anton's Cajun Seasoning
5           Chef Anton's Gumbo Mix
6           Grandma's Boysenberry Spread
8           Northwoods Cranberry Sauce
15          Genen Shouyu
44          Gula Malacca
61          Sirop d'érable
63          Vegie-spread
65          Louisiana Fiery Hot Pepper Sauce
66          Louisiana Hot Spiced Okra
77          Original Frankfurter grüne Soße
10          Ikura
13          Konbu
18          Carnarvon Tigers
30          Nord-Ost Matjeshering
36          Inlagd Sill
37          Gravad lax
40          Boston Crab Meat
41          Jack's New England Clam Chowder
45          Rogede sild
46          Spegesild
58          Escargots de Bourgogne
73          Röd Kaviar（所影响的行数为 36 行）*/

Answer 2

select * from products a where a.unitsinstock in (select top 3 b.unitsinstock from products b where b.catagoryid = a.catagoryid order by b.unitsinstock)

Answer 3

或者试试 
select catagoryid, row_number() over (order by catagoryid desc) as SubSeq, unitsinstock from products

Answer 4

select categoryid,
row_number() over (partition by categoryid order by unitsinstock desc) as subseq,
unitsinstock into #
from products/*
categoryid  subseq               unitsinstock
----------- -------------------- ------------
1           1                    125
1           2                    111
1           3                    69
1           4                    57
1           5                    52
1           6                    39
1           7                    20
1           8                    20
1           9                    17
1           10                   17
1           11                   17
1           12                   15
2           1                    120
2           2                    113
2           3                    76
2           4                    53
2           5                    39
2           6                    32
2           7                    27
2           8                    24
2           9                    13
2           10                   6
2           11                   4
2           12                   0
3           1                    76
3           2                    65
3           3                    49
3           4                    40
3           5                    36
3           6                    29
3           7                    25
3           8                    17
3           9                    15
3           10                   15
3           11                   10
3           12                   6
3           13                   3
4           1                    112
4           2                    86
4           3                    79
4           4                    26
4           5                    26
4           6                    22
4           7                    19
4           8                    14
4           9                    9
4           10                   0
5           1                    104
5           2                    61
5           3                    38
5           4                    36
5           5                    26
5           6                    22
5           7                    21
6           1                    115
6           2                    29
6           3                    21
6           4                    0
6           5                    0
6           6                    0
7           1                    35
7           2                    26
7           3                    20
7           4                    15
7           5                    4
8           1                    123
8           2                    112
8           3                    101
8           4                    95
8           5                    85
8           6                    62
8           7                    42
8           8                    31
8           9                    24
8           10                   11
8           11                   10
8           12                   5(77 row(s) affected)*/select categoryid,unitsinstock from # where subseq <4
go/*
categoryid  unitsinstock
----------- ------------
1           125
1           111
1           69
2           120
2           113
2           76
3           76
3           65
3           49
4           112
4           86
4           79
5           104
5           61
5           38
6           115
6           29
6           21
7           35
7           26
7           20
8           123
8           112
8           101(24 row(s) affected)*/

Answer 5

select aaa.ProductID,aaa.ProductName,bbb.库存总数 from Products aaa,
(select top 3 CategoryID ,sum(UnitsInStock) as 库存总数 from Products group by CategoryID order by 库存总数 desc) bbb
where aaa.CategoryID=bbb.CategoryID

Answer 6

select m.ProductID,ProductName from Products m
right join (select top 3 CategoryID , UnitsInStock = sum(UnitsInStock) 
            from Products 
            group by CategoryID  
            order by UnitsInStock desc) n
on m.CategoryID = n.CategoryID
order by m.CategoryID

Answer 7

这样查询，看结果更加明了
select aaa.CategoryID,aaa.ProductID,aaa.ProductName,sum(aaa.UnitsInStock) as 产品总数,bbb.分类总数 from Products aaa,
(select top 3 CategoryID ,sum(UnitsInStock) as 分类总数 from Products group by CategoryID order by 分类总数 desc) bbb
where aaa.CategoryID=bbb.CategoryID group by aaa.ProductID,aaa.ProductName,aaa.CategoryID,bbb.分类总数
order by bbb.分类总数 desc,产品总数 desc