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


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

 

建表脚本

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

SQL Server -- ROW_NUMBER () 与 PARTITION 组合妙用_sql_02SQL Server -- ROW_NUMBER () 与 PARTITION 组合妙用_sql_03代码

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

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

 


SQL Server -- ROW_NUMBER () 与 PARTITION 组合妙用_sql_05SQL Server -- ROW_NUMBER () 与 PARTITION 组合妙用_sql_06代码

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

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

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


-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)

下面就是解决问题的脚本

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

SQL Server -- ROW_NUMBER () 与 PARTITION 组合妙用_sql_10SQL Server -- ROW_NUMBER () 与 PARTITION 组合妙用_sql_11代码

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