SQL Server -- ROW_NUMBER () 与 PARTITION 组合妙用

From: http://www.cnblogs.com/kerrycode/archive/2010/12/27/1918218.html 

前几天在一个群里面，有位网友问：在一个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
       )
       

-ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)

--ROW_NUMBER()1

--ROW_NUMBER() OVER (ORDER BY xlh DESC) xlhxlh

--ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2) COL1 COL2)

下面就是解决问题的脚本

代码

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://jingyan.baidu.com/article/9989c74604a644f648ecfef3.html 

http://blog.csdn.net/631799/article/details/7419797

  

转载于:https://blog.51cto.com/57388/1867963