ROW_NUMBER () 与 PARTITION 组合妙用

前几天在一个群里面，有位网友问：在一个Book表里面里有字段AuthorID与Author表关联，现在要求按PublishDate字段倒序排列，列出每个作者的前五本书。要求有没有一条语句搞定的. 当时有个网友说不能一条语句解决问题，说只能用游标或临时表来解决。恰好我前阵子在整报表时遇到过类似的问题，当时解决过这个问题。当时我就告诉他用ROW_NUMBER与PARTITION来解决(前提是SQL SERVER 05或以上版本)。恰好现在有时间。正好把这个整理一下，即是对知识的梳理、巩固、总结，也希望能给其他人一些帮助

 

建表脚本

IF OBJECT_ID(N'Author') IS NOT NULL
BEGIN    
    DROP TABLE dbo.Author;
END
ELSE
    BEGIN
        CREATE TABLE dbo.Author
        (
            AuthorID      INT IDENTITY(1,1) PRIMARY KEY,
            AuthorName    NVARCHAR(50),
            NickName      NVARCHAR(50),
            Place         NVARCHAR(120),
            BirthDay      SMALLDATETIME
        )
    END
GO

IF OBJECT_ID(N'Book') IS NOT NULL 
    BEGIN
        DROP TABLE dbo.Book ;
    END
ELSE 
    BEGIN
    
        CREATE TABLE dbo.Book
        (
            ID                    INT IDENTITY(1, 1) ,
            BookName              NVARCHAR(35) ,            --书名
            PublishDate           DATETIME ,                --出版时间
            Publisher             NVARCHAR(50) ,            --出版商
            BookType              INT ,                     --书籍类型
            AuthorID              INT FOREIGN KEY REFERENCES dbo.Author(AuthorID)
        )
    END
GO

 

--生成实验数据
INSERT INTO dbo.Author
VALUES('张三', '三峰', '北京', '1973-12-28')

INSERT INTO dbo.Author
VALUES ('王五', '绝望的中春天', '湖南', '1978-5-23' )

INSERT INTO dbo.Author
VALUES ('赵四', '赵四', '上海', '1978-5-23' )


INSERT INTO dbo.Book
        ( BookName ,
          PublishDate ,
          Publisher ,
          BookType ,
          AuthorID
        )
VALUES  ( '张三书1' , 
          '1988-12-24' ,
          '北京图书出版社' , 
          1 , 
          1 
        )
        
        
INSERT INTO dbo.Book
        ( BookName ,
          PublishDate ,
          Publisher ,
          BookType ,
          AuthorID
        )
VALUES  ( '张三书2' , 
          '1983-12-04' ,
          '长城图书出版社' , 
          2 , 
          1 
        )
       
       
INSERT INTO dbo.Book
        ( BookName ,
          PublishDate ,
          Publisher ,
          BookType ,
          AuthorID
        )
VALUES  ( '张三书3' , 
          '1995-12-19' ,
          '教育图书出版社' , 
          2 , 
          1 
        )
        
INSERT INTO dbo.Book
        ( BookName ,
          PublishDate ,
          Publisher ,
          BookType ,
          AuthorID
        )
VALUES  ( '张三书4' , 
          '1996-12-04' ,
          '教育图书出版社' , 
          2 , 
          1 
        )
        
INSERT INTO dbo.Book
        ( BookName ,
          PublishDate ,
          Publisher ,
          BookType ,
          AuthorID
        )
VALUES  ( '张三书5' , 
          '2004-04-26' ,
          '教育图书出版社' , 
          2 , 
          1 
        )
        
        
INSERT INTO dbo.Book
        ( BookName ,
          PublishDate ,
          Publisher ,
          BookType ,
          AuthorID
        )
VALUES  ( '张三书6' , 
          '2009-12-15' ,
          '教育图书出版社' , 
          2 , 
          1 
        )
        
INSERT INTO dbo.Book
        ( BookName ,
          PublishDate ,
          Publisher ,
          BookType ,
          AuthorID
        )
VALUES  ( '王五1' , 
          '2003-06-15' ,
          '教育图书出版社' , 
          2 , 
         2 
        )
        
INSERT INTO dbo.Book
        ( BookName ,
          PublishDate ,
          Publisher ,
          BookType ,
          AuthorID
        )
VALUES  ( '王五2' , 
          '2007-09-25' ,
          '上海图书出版社' , 
          1 , 
          2 
        )
        
        
INSERT INTO dbo.Book
        ( BookName ,
          PublishDate ,
          Publisher ,
          BookType ,
          AuthorID
        )
VALUES  ( '赵四1' , 
          '2010-09-25' ,
          '上海图书出版社' , 
          1 , 
          3 
        )
        

 

下面就是解决问题的脚本

SELECT * FROM
     (
         SELECT  ROW_NUMBER() OVER (PARTITION  BY A.AuthorID ORDER BY  B.PublishDate DESC) AS RowNum,
                 A.AuthorName, B.BookName, B.PublishDate
         FROM 
         dbo.Book B
         INNER JOIN dbo.Author A ON A.AuthorID = B.AuthorID
     ) T
     WHERE T.RowNum <= 5