sql 分组取最新的多寡sqlserver巧用row_number和partition by分组取top数据

SQL Server
2005后之后,引入了row_number()函数,row_number()函数的分组排序功能使这种操作变得非常简单

CREATE TABLE score
(
name NVARCHAR(20),
subject NVARCHAR(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)

分组取TOP数据是T-SQL中的常用查询,
如学生信息管理系统中取出每个学科前3名的学生。这种查询在SQL Server
2005之前,写起来很繁琐,需要用到临时表关联查询才能取到。SQL Server
2005后之后,引入了row_number()函数,row_number()函数的分组排序功能使这种操作变得非常简单。下面是一个简单示例:

select * from score

 

select subject from score   for xml path(”)
SELECT Subject+’,’  FROM score
    where name=’林风’
  FOR XML PATH(”)
 
   SELECT  cast(score as varchar)+’,’ FROM score
    where name=’林风’
  FOR XML PATH(”)
 
SELECT name ,sum(score),
(SELECT Subject+’,’ FROM score
  WHERE name=A.name
  FOR XML PATH(”)) AS StuList,
 
 
  (SELECT cast(score as varchar(10))+’,’ FROM score
  WHERE name=A.name
  FOR XML PATH(”)) AS StuList
 
 
FROM score A
GROUP BY name

–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)
澳门金沙vip,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 order by subject
–4.删除临时表
truncate table #score
drop table #score

–3.取每个学科的前3名数据