实例数据对比SQL中的ROW_NUMBER()、RANK()、DENSE_RANK()、NTILE()，一目了然

01 USE TestDB
02
03 IF OBJECT_ID ( 't_Test' , 'U' ) IS NOT NULL
04     DROP TABLE t_Test
05 CREATE TABLE t_Test
06     (
07       [ id ] INT UNIQUE
08                NOT NULL ,
09       [ name ] NVARCHAR ( 20 ),
10       [ grade ] DECIMAL ( 8 , 2 ),
11     ) ;
12 INSERT   INTO [ t_Test ]
13 VALUES   ( 1 , N '一班' , 100 . 0 ),
14         ( 2 , N '二班' , 90 . 5 ),
15         ( 3 , N '一班' , 90 . 5 ),
16         ( 4 , N '二班' , 70 . 0 ),
17         ( 5 , N '二班' , 55 . 5 ) ,
18         ( 6 , N '三班' , 95 . 0 ),
19         ( 7 , N '一班' , 90 . 5 ),
20         ( 8 , N '二班' , 80 . 0 ),
21         ( 9 , N '二班' , 80 . 0 ),
22         ( 10 , N '三班' , 90 . 0 ),
23         ( 11 , N '二班' , 100 . 0 ),
24         ( 12 , N '一班' , 85 . 0 ) ;
25
26 SELECT   * ,
27         [ ROW_NUMBER ()] = ROW_NUMBER () OVER ( PARTITION BY [ name ] ORDER BY [ grade ] ),
28         [ RANK ()] = RANK () OVER ( PARTITION BY [ name ] ORDER BY [ grade ] ),
29         [ DENSE_RANK ()] = DENSE_RANK () OVER ( PARTITION BY [ name ] ORDER BY [ grade ] ),
30         [ NTILE ()] = NTILE ( 4 ) OVER ( PARTITION BY [ name ] ORDER BY [ grade ] )
31 FROM     t_Test ;
32
33
34
35 DROP TABLE t_Test ;
36
37 /*
38 (12 行受影响)
39 id          name                 grade                                   ROW_NUMBER()         RANK()               DENSE_RANK()         NTILE()
40 ----------- -------------------- --------------------------------------- -------------------- -------------------- -------------------- --------------------
41 5           二班                   55.50                                   1                    1                    1                    1
42 4           二班                   70.00                                   2                    2                    2                    1
43 8           二班                   80.00                                   3                    3                    3                    2
44 9           二班                   80.00                                   4                    3                    3                    2
45 2           二班                   90.50                                   5                    5                    4                    3
46 11          二班                   100.00                                  6                    6                    5                    4
47 10          三班                   90.00                                   1                    1                    1                    1
48 6           三班                   95.00                                   2                    2                    2                    2
49 12          一班                   85.00                                   1                    1                    1                    1
50 7           一班                   90.50                                   2                    2                    2                    2
51 3           一班                   90.50                                   3                    2                    2                    3
52 1           一班                   100.00                                  4                    4                    3                    4
53
54 (12 行受影响)
55
56 */