整理最近新学到的几种sql写法（二）“取得每个班里的前两名”

我们经常会遇到如“取得每个班里的前两名”之类的问题，解决方法如下：

 

 1 CREATE TABLE t2
 2 (
 3     id INT IDENTITY,
 4     vUserName NVARCHAR(50),
 5     vClassName NVARCHAR(50),
 6     iGrade    int
 7 )
 8 
 9 
10 INSERT INTO [t2] (
11     [vUserName],
12     [vClassName],
13     [iGrade]
14 ) 
15 SELECT 'user1', 'class1', 20
16 UNION
17 SELECT 'user2', 'class1', 40
18 UNION 
19 SELECT 'user3', 'class2', 30
20 UNION
21 SELECT 'user4', 'class1', 50
22 UNION
23 SELECT 'user5', 'class1', 30
24 UNION
25 SELECT 'user6', 'class2', 40
26 UNION
27 SELECT 'user7', 'class2', 34
28 
29 SELECT    *
30 FROM    [t2]
31 
32 --1
33 SELECT    *
34 FROM    [t2] AS t
35 WHERE    t.[id] IN (
36             SELECT    TOP 2 [id]
37             FROM    [t2] AS s
38             WHERE    t.[vClassName] = s.[vClassName]
39             ORDER BY s.[iGrade] DESC
40             )
41 
42 --2
43 SELECT    * 
44 FROM    [t2] T 
45 WHERE    CHECKSUM(*) IN (
46             SELECT TOP 2  CHECKSUM(*) 
47             FROM    [t2] 
48             WHERE    [vClassName]=t.[vClassName] 
49             ORDER BY [iGrade] DESC
50         )
51             
52 --3
53 SELECT    *
54 FROM    [t2] AS t
55 WHERE    2 >= (
56             SELECT COUNT(1)
57             FROM [t2] AS s 
58             WHERE t.[vClassName] = s.[vClassName] 
59                 AND t.[iGrade] <= s.[iGrade]
60             )
61