--PARTITION by和ROW_NUMBER() over的用法
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)
--这条语句可以根据学科分组,根据分数进行排列,最后ROW_NUMBER() over会为每个分组进行排名编号,可以看到排名那里123..名
select *,ROW_NUMBER() over( partition by subject order by score )排名
from #score
mysql示例
SELECT @num := @num + 1 as num,''as AA1,'' as AA2 FROM
(select 0 union all select 1 union all select 2 ) t1,
(select 0 union all select 1 union all select 2 ) t2,
(select 0 union all select 1 union all select 2 ) t3,
(select 0 union all select 1 union all select 2 ) t4,
(select 0 union all select 1 ) t5,
(SELECT @num:=0) r