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

来源：互联网发布：android 源码目录结构编辑：程序博客网时间：2024/05/13 15:52
USE TestDB

IF OBJECT_ID('t_Test', 'U') IS NOT NULL 
    DROP TABLE t_Test
CREATE TABLE t_Test
    (
      [id] INT UNIQUE
               NOT NULL,
      [name] NVARCHAR(20),
      [grade] DECIMAL(8, 2),
    ) ;
INSERT  INTO [t_Test]
VALUES  ( 1, N'一班', 100.0 ),
        ( 2, N'二班', 90.5 ),
        ( 3, N'一班', 90.5 ),
        ( 4, N'二班', 70.0 ),
        ( 5, N'二班', 55.5 ) ,
        ( 6, N'三班', 95.0 ),
        ( 7, N'一班', 90.5 ),
        ( 8, N'二班', 80.0 ),
        ( 9, N'二班', 80.0 ),
        ( 10, N'三班', 90.0 ),
        ( 11, N'二班', 100.0 ),
        ( 12, N'一班', 85.0 ) ;

SELECT  *,
        [ROW_NUMBER()] = ROW_NUMBER() OVER ( PARTITION BY [name] ORDER BY [grade] ),
        [RANK()] = RANK() OVER ( PARTITION BY [name] ORDER BY [grade] ),
        [DENSE_RANK()] = DENSE_RANK() OVER ( PARTITION BY [name] ORDER BY [grade] ),
        [NTILE()] = NTILE(4) OVER ( PARTITION BY [name] ORDER BY [grade] )
FROM    t_Test ;



DROP TABLE t_Test ;

/*
(12 行受影响)
id          name                 grade                                   ROW_NUMBER()         RANK()               DENSE_RANK()         NTILE()
----------- -------------------- --------------------------------------- -------------------- -------------------- -------------------- --------------------
5           二班                   55.50                                   1                    1                    1                    1
4           二班                   70.00                                   2                    2                    2                    1
8           二班                   80.00                                   3                    3                    3                    2
9           二班                   80.00                                   4                    3                    3                    2
2           二班                   90.50                                   5                    5                    4                    3
11          二班                   100.00                                  6                    6                    5                    4
10          三班                   90.00                                   1                    1                    1                    1
6           三班                   95.00                                   2                    2                    2                    2
12          一班                   85.00                                   1                    1                    1                    1
7           一班                   90.50                                   2                    2                    2                    2
3           一班                   90.50                                   3                    2                    2                    3
1           一班                   100.00                                  4                    4                    3                    4

(12 行受影响)

*/