数据来源:题目所需文件
tname(姓名)、fr_grade(FR得分)、sql_grade(SQL得分)、tomcat_grade(TOMCAT得分)、kettle_grade(kettle得分)、inserttime (考核时间)
其中KETTLE不是必考项是加分项，考核通过的要求是∶FR、SQL、TOMCAT三个模块得分均超过60分同时四个模块总分超过240。
题目：
考核通过优于考核未通过，总分高的优于总分低的，已知张三考了多次，查询他每次考核较上次考核是进步还是退步，结果输出考核日期、进退步情况。
-- 建表语句
CREATE TABLE `sql2002` (
`tname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`fr_grade` int NULL DEFAULT NULL,
`sql_grade` int NULL DEFAULT NULL,
`tomcat_grade` int NULL DEFAULT NULL,
`kettle_grade` int NULL DEFAULT NULL,
`inserttime` date NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- 插入数据
INSERT INTO `sql2002` VALUES ('陈一', 40, 68, 68, 39, '2021-05-20');
INSERT INTO `sql2002` VALUES ('陈一', 43, 50, 81, 33, '2021-06-05');
INSERT INTO `sql2002` VALUES ('李四', 41, 30, 36, 93, '2021-05-23');
INSERT INTO `sql2002` VALUES ('李四', 60, 67, 60, 14, '2021-05-29');
INSERT INTO `sql2002` VALUES ('李四', 52, 63, 76, 62, '2021-06-01');
INSERT INTO `sql2002` VALUES ('林二', 72, 79, 68, 55, '2021-05-21');
INSERT INTO `sql2002` VALUES ('林二', 63, 81, 60, 11, '2021-05-27');
INSERT INTO `sql2002` VALUES ('林二', 75, 85, 90, 89, '2021-05-30');
INSERT INTO `sql2002` VALUES ('王五', 50, 72, 89, 84, '2021-05-24');
INSERT INTO `sql2002` VALUES ('王五', 94, 33, 53, 88, '2021-06-02');
INSERT INTO `sql2002` VALUES ('张三', 56, 31, 41, 67, '2021-05-22');
INSERT INTO `sql2002` VALUES ('张三', 80, 66, 72, 72, '2021-05-28');
INSERT INTO `sql2002` VALUES ('张三', 85, 59, 29, 34, '2021-05-31');
INSERT INTO `sql2002` VALUES ('赵七', 42, 77, 58, 87, '2021-05-26');
INSERT INTO `sql2002` VALUES ('赵七', 83, 95, 65, 21, '2021-06-04');
INSERT INTO `sql2002` VALUES ('赵七', 93, 25, 32, 70, '2021-06-07');
INSERT INTO `sql2002` VALUES ('周六', 98, 61, 80, 28, '2021-05-25');
INSERT INTO `sql2002` VALUES ('周六', 44, 84, 28, 99, '2021-06-03');
INSERT INTO `sql2002` VALUES ('周六', 71, 62, 56, 79, '2021-06-06');
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
你有什么初始的思路么？