ROW_NUMBER () 与 PARTITION 组合妙用

时间：2010-12-27 来源：潇湘隐者

建表脚本

代码

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