参考 https://ask.csdn.net/questions/7766892
with temp1 as
(select t.tname,
t.inserttime,
case when t.fr_grade>60 and t.sql_grade>60 and t.tomcat_grade>60 and (t.fr_grade+t.sql_grade+t.tomcat_grade+t.kettle_grade)>240 --三门必修均大于60且四门总大于240
then 1
else 0 end as is_pass, --是否考核通过
t.fr_grade+t.sql_grade+t.tomcat_grade+t.kettle_grade as sum_grade --四门成绩总和
from sql2002 t
order by t.tname,t.inserttime)
select b.tname
,b.inserttime
,case when b.lag_is_pass is null then null --上一考核日期不存在数据
when b.lag_is_pass is not null and b.is_pass>b.lag_is_pass then '提高' --本次考核通过,上次未通过
when b.lag_is_pass is not null and b.is_pass=b.lag_is_pass and b.sum_grade>b.lag_sum_grade then '提高' --本次和上次考核结果相同,本次总成绩比上次高
when b.lag_is_pass is not null and b.is_pass=b.lag_is_pass and b.sum_grade=b.lag_sum_grade then '持平' --考核结果和成绩都相同
else '退步' end as is_grade_up --成绩是否提高
from
(select a.tname
,a.inserttime
,a.is_pass
,a.sum_grade
,lag(a.is_pass)over(partition by a.tname order by inserttime) as lag_is_pass --上一考试日期是否通过
,lag(a.sum_grade)over(partition by a.tname order by inserttime) as lag_sum_grade --上一考试日期四门成绩总和
from temp1 a) b