找出10次考试中成绩连续下滑3次的学生

最新推荐文章于 2023-03-07 15:54:09 发布

最新推荐文章于 2023-03-07 15:54:09 发布 · 98 阅读

本文通过一段SQL代码示例展示了如何从多次考试记录中找出学生成绩连续三次下滑的情况。利用WITH子句创建临时表，并使用连接递归查询实现成绩趋势判断。

问题：从10次考试中，找出考试成绩连续下滑3次的学生姓名？

解法：

SQL> with tb as(
2 select 'aspen' sname,to_date('20100111','yyyymmdd')ttime,100 score from dual
3 union all
4 select 'aspen',to_date('20100211','yyyymmdd'),90 from dual
5 union all
6 select 'aspen',to_date('20100311','yyyymmdd'),80 from dual
7 union all
8 select 'aspen',to_date('20100411','yyyymmdd'),82 from dual
9 union all
10 select 'aspen',to_date('20100511','yyyymmdd'),81 from dual
11 union all
12 select 'aspen',to_date('20100611','yyyymmdd'),79 from dual
13 union all
14 select 'aspen',to_date('20100711','yyyymmdd'),77 from dual
15 union all
16 select 'aspen',to_date('20100811','yyyymmdd'),70 from dual
17 union all
18 select 'scott',to_date('20100111','yyyymmdd'),70 from dual
19 union all
20 select 'scott',to_date('20100211','yyyymmdd'),60 from dual
21 union all
22 select 'scott',to_date('20100311','yyyymmdd'),90 from dual
23 )
24 select distinct sname
25 from
26 (select sname,
27 ttime,
28 score,
29 row_number() over(partition by sname order by ttime) rn
30 from tb) t
31 where level>=4
32 start with rn=rn
33 connect by prior score>score and prior rn=rn-1
34 /

SNAME
-----
aspen