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

作者：潇湘隱者

出處：http://www.cnblogs.com/kerrycode/