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
