ROW_NUMBER () 与 PARTITION组合拳
--在一个Book表里面里有字段AuthorID与Author表关联，现在要求按PublishDate字段倒序排列，列出每个作者的前五本书。要求有没有一条语句搞定的
--可用游标或者临时表
--最好解决方法： 使用 ROW_NUMBER () 与 PARTITION 组合
例子：
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 dbo.Book
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
row_number() OVER ( PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序
顺便来几个排序：
row_number()和rownum差不多，功能更强一点（可以在各个分组内从1开时排序）．
rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）．
dense_rank()是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的.
