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

ispsh

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

http://common.cnblogs.com/images/copycode.gif

http://images.cnblogs.com/OutliningIndicators/ContractedBlock.gifhttp://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif代码

　　IF OBJECT_ID(N'Author') IS NOT NULL
　　BEGIN
　　DROP TABLE dbo.Author;
　　END
　　ELSE
　　BEGIN
　　CREATE TABLE dbo.Author
　　(

　　AuthorID      INT>　　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>　　BookName              NVARCHAR(35) ,            --书名
　　PublishDate           DATETIME ,                --出版时间
　　Publisher             NVARCHAR(50) ,            --出版商
　　BookType              INT ,                     --书籍类型
　　AuthorID              INT FOREIGN KEY REFERENCES dbo.Author(AuthorID)
　　)
　　END
　　GO

http://common.cnblogs.com/images/copycode.gif

http://images.cnblogs.com/OutliningIndicators/ContractedBlock.gifhttp://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif代码

http://common.cnblogs.com/images/copycode.gif

　　--生成实验数据
　　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
　　)

http://common.cnblogs.com/images/copycode.gif

　　-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)
　　下面就是解决问题的脚本

http://common.cnblogs.com/images/copycode.gif

http://images.cnblogs.com/OutliningIndicators/ContractedBlock.gifhttp://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif代码

　　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