SQL SERVER 函数ROW_NUMBER的使用汇集

--参阅http://www.2cto.com/database/201303/194530.html
--通过上述对比不难发现，在面临Group获取Top N场景时，可以首选row_number，游标cursor其次，
CREATE TABLE [dbo].[products](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](50) NULL,
    [addtime] [datetime] NULL,
    [city] [nvarchar](10) NULL,
 CONSTRAINT [PK_products] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


--插入10万条数据 格式为‘笔记本1,2,3,4...’，getdate(),'北京1,北京2,北京3.....'


DECLARE @NUM INT
SET @NUM=1
WHILE(@NUM<100000)
BEGIN
INSERT INTO products VALUES('笔记本'+CONVERT(VARCHAR(256),@NUM),GETDATE(),'北京'+CONVERT(VARCHAR(256),@NUM));
SET @NUM=@NUM+1;
END


update products set city='北京' WHERE city LIKE '北京1%'
update products set city='上海' WHERE city LIKE '北京2%'
update products set city='广州' WHERE city LIKE '北京3%'
update products set city='深圳' WHERE city LIKE '北京4%'
update products set city='武汉' WHERE city LIKE '北京5%'
update products set city='西安' WHERE city LIKE '北京6%'
update products set city='成都' WHERE city LIKE '北京7%'
update products set city='天津' WHERE city LIKE '北京8%'
update products set city='南京' WHERE city LIKE '北京9%'
select * from products
--有产品表，包含id，name，city，addtime四个字段，因报表需要按城市分组，统计每个城市的最新10个产品


SELECT * FROM 
(SELECT ROW_NUMBER() OVER(PARTITION BY CITY ORDER BY ID DESC) AS SEQ,* FROM products) AS REST
WHERE SEQ<10


select no, id,name,city 
from  (select  no =row_number() over (partition by city order by addtime desc), * from products)t
where no< 11 order by city asc,addtime desc








--ROW_NUMBER函数使用
create database StudentDB
go
use StudentDB
go
create table Student  --学生成绩表
(
 id int,  --主键
 Grade int, --班级
 Score int --分数
)
go


insert Student 
    select 1,1,88
union all select 2,1,66
union all select 3,1,75
union all select 4,2,30
union all select 5,2,70
union all select 6,2,80
union all select 7,2,60
union all select 8,3,90
union all select 9,3,70
union all select 10,3,80


go


--整个数据进行排名
SELECT ROW_NUMBER() OVER(ORDER BY SCORE DESC) AS '排名', * FROM Student 
--整个数据分组后进行排名
SELECT ROW_NUMBER() OVER(PARTITION BY GRADE ORDER BY SCORE DESC) AS '排名',* FROM Student --ORDER BY Score DESC


--partition  by关键字是分析性函数的一部分，它和聚合函数不同的地方在于它能返回一个分组中的多条记录，
--而聚合函数一般只有一条反映统计值的记录，
--partition  by用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组