[MySQL] mysql使用自定义序列实现row_number功能(步骤详解)

create table grades(
`name` varchar(10),
`subject` varchar(10),
`score` int(10)
)

insert into grades(name, subject, score)
values('小明', '语文', 85),
('小华', '语文', 89),
('小李', '语文', 91),
('小芳', '语文', 93),
('小明', '数学', 77),
('小华', '数学', 95),
('小李', '数学', 83),
('小芳', '数学', 88),
('小明', '英语', 90),
('小华', '英语', 92),
('小李', '英语', 85),
('小芳', '英语', 88)

select name, subject, score 
from grades
order by subject, score desc

select (@i:=case when @subject_pre=t1.subject then @i+1 else 1 end) as rn,
t1.*,
(@subject_pre:=subject) 
from (
  select name, subject, score 
  from grades
  order by subject, score desc
) t1, 
(select @i:=0, @subject_pre:='') as t2
group by subject, score
order by subject, score desc

select name, subject, score from(
select (@i:=case when @subject_pre=t1.subject then @i+1 else 1 end) as rn,
t1.name,
t1.subject,
t1.score,
(@subject_pre:=subject) 
from (
select name, subject, score 
from grades
order by subject, score desc
) t1, 
(select @i:=0, @subject_pre:='') as t2
group by subject, score
order by subject, score desc
) t
where rn=2