Partition By及row_number 函数

partition by用于给结果集分组;     row_number 添加行号

先看例子

create table TESTDB  (A varchar(8), B varchar(8))

insert into TESTDB
select 'A1', 'B1' union all
select 'A1', 'B2' union all
select 'A1', 'B3' union all
select 'A2', 'B4' union all
select 'A2', 'B5' union all
select 'A2', 'B6' union all
select 'A3', 'B7' union all
select 'A3', 'B3' union all
select 'A3', 'B4'

一. 仅仅使用ROW_NUMBER() OVER的结果

SELECT *,   ROW_NUMBER()  OVER ( ORDER BY A )    as NUM         FROM TESTDB

-----单纯标出了行号

A  B   NUM
----------
A3  B7   1
A3  B3   2
A3  B4   3
A2  B4   4
A2  B5   5
A2  B6   6
A1  B1   7
A1  B2   8
A1  B3   9

二 .  使用PARTITION BY 函数分组

SELECT  *,  ROW_NUMBER()  OVER  ( PARTITION BY A ORDER BY A )    as NUM          FROM TESTDB

---对A分组，比如A1有3个，他就给每个A1标上是第几个

A  B  NUM
-------------
A1  B1  1
A1  B2  2
A1  B3  3
A2  B4  1
A2  B5  2
A2  B6  3
A3  B7  1
A3  B3  2
A3  B4  3

row_number行号函数和partition by分组函数取top数据

--1.创建测试表 
create table #score 
( 
name varchar(20), 
subject varchar(20), 
score int 
) 
--2.插入测试数据 
insert into #score(name,subject,score) values('张三','语文',98) 
insert into #score(name,subject,score) values('张三','数学',80) 
insert into #score(name,subject,score) values('张三','英语',90) 
insert into #score(name,subject,score) values('李四','语文',88) 
insert into #score(name,subject,score) values('李四','数学',86) 
insert into #score(name,subject,score) values('李四','英语',88) 
insert into #score(name,subject,score) values('李明','语文',60) 
insert into #score(name,subject,score) values('李明','数学',86) 
insert into #score(name,subject,score) values('李明','英语',88) 
insert into #score(name,subject,score) values('林风','语文',74) 
insert into #score(name,subject,score) values('林风','数学',99) 
insert into #score(name,subject,score) values('林风','英语',59) 
insert into #score(name,subject,score) values('严明','英语',96) 
--3.取每个学科的前3名数据 
select * from 
( 
select subject,name,score,ROW_NUMBER() over(PARTITION by subject order by score desc) as num from #score 
) T 
where T.num <= 3