-- 1.连续3次成绩上升
with t as
(select id,
StudentName,
Score,
row_number() over(partition by StudentName order by id desc) 'rn'
from #t)
select distinct a.id,a.StudentName
from t a
inner join t b on a.StudentName=b.StudentName
inner join t c on a.StudentName=c.StudentName
where a.rn=1 and b.rn=2 and c.rn=3
and a.Score>b.Score and b.Score>c.Score
/*
id StudentName
----------- -----------
34 李同学
38 王同学
(2 row(s) affected)
*/
-- 2.连续3次成绩下降
with t as
(select id,
StudentName,
Score,
row_number() over(partition by StudentName order by id desc) 'rn'
from #t)
select distinct a.id,a.StudentName
from t a
inner join t b on a.StudentName=b.StudentName
inner join t c on a.StudentName=c.StudentName
where a.rn=1 and b.rn=2 and c.rn=3
and a.Score<b.Score and b.Score<c.Score
/*
id StudentName
----------- -----------
35 赵同学
(1 row(s) affected)
*/
摘自CSDN问答，问答网址：https://bbs.csdn.net/topics/391059033