这道SQL题应该怎么写？

Question

数据来源:题目所需文件
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');

Answer 1

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