如何在SQL自连接条件中实现if-then逻辑计算学生技能得分增长
学生技能得分增长计算SQL实现方案
核心需求拆解
- 对每个「学生+技能」组合,定位当前得分年之前1~3年内最近的有效得分年作为对比基准
- 基准优先级规则:间隔1年 > 间隔2年 > 间隔3年,已有更高优先级基准时,低优先级的关联自动失效
- 最终输出得分增长最高的学生、技能及对应增长值
原有自连逻辑的问题
直接把三个间隔条件用OR拼接的写法,会让同一目标年同时匹配多个基准年(比如2002年同时匹配2001年、2000年的得分),产生重复结果,不会自动按优先级过滤。
推荐实现方案(窗口函数版)
适用于支持CTE、窗口函数的数据库(MySQL8.0+、PostgreSQL、SQL Server等),逻辑统一且性能更好:
WITH candidate_base AS ( SELECT cur.student_name, cur.skill_category, cur.score_year AS target_year, cur.score_val AS target_score, base.score_year AS base_year, base.score_val AS base_score, -- 按间隔打优先级,数值越小优先级越高 CASE WHEN base.score_year = cur.score_year - 1 THEN 1 WHEN base.score_year = cur.score_year - 2 THEN 2 WHEN base.score_year = cur.score_year - 3 THEN 3 END AS priority FROM student_skill_score cur LEFT JOIN student_skill_score base ON cur.student_name = base.student_name AND cur.skill_category = base.skill_category -- 仅关联前1~3年的可能基准年 AND base.score_year IN (cur.score_year - 1, cur.score_year - 2, cur.score_year - 3) ), valid_growth AS ( SELECT *, -- 同一目标年下,取优先级最高的基准记录 ROW_NUMBER() OVER ( PARTITION BY student_name, skill_category, target_year ORDER BY priority ASC ) AS rn FROM candidate_base -- 过滤掉无有效基准年的记录 WHERE base_score IS NOT NULL ) -- 计算增长并按降序排序 SELECT student_name, skill_category, base_year, target_year, base_score, target_score, target_score - base_score AS growth_val FROM valid_growth WHERE rn = 1 ORDER BY growth_val DESC -- 仅取增长最高的1条,需要所有增长记录则删掉该限制 LIMIT 1;
注:请将表名
student_skill_score、字段名替换为你业务库中的实际命名
兼容老版本数据库的方案(UNION版)
如果使用不支持窗口函数的老版本数据库,可以通过三段查询加过滤条件实现优先级逻辑,避免重复匹配:
-- 1. 优先匹配间隔1年的记录 SELECT student_name, skill_category, score_year AS base_year, target_year, score_val AS base_score, target_score, target_score - score_val AS growth_val FROM ( SELECT cur.*, base.score_val, base.score_year FROM student_skill_score cur INNER JOIN student_skill_score base ON cur.student_name = base.student_name AND cur.skill_category = base.skill_category AND base.score_year = cur.score_year - 1 ) t1 UNION ALL -- 2. 仅匹配无间隔1年数据的间隔2年记录 SELECT student_name, skill_category, score_year AS base_year, target_year, score_val AS base_score, target_score, target_score - score_val AS growth_val FROM ( SELECT cur.*, base.score_val, base.score_year FROM student_skill_score cur INNER JOIN student_skill_score base ON cur.student_name = base.student_name AND cur.skill_category = base.skill_category AND base.score_year = cur.score_year - 2 -- 排除已经匹配到间隔1年基准的目标年 WHERE NOT EXISTS ( SELECT 1 FROM student_skill_score b1 WHERE b1.student_name = cur.student_name AND b1.skill_category = cur.skill_category AND b1.score_year = cur.score_year - 1 ) ) t2 UNION ALL -- 3. 仅匹配无间隔1/2年数据的间隔3年记录 SELECT student_name, skill_category, score_year AS base_year, target_year, score_val AS base_score, target_score, target_score - score_val AS growth_val FROM ( SELECT cur.*, base.score_val, base.score_year FROM student_skill_score cur INNER JOIN student_skill_score base ON cur.student_name = base.student_name AND cur.skill_category = base.skill_category AND base.score_year = cur.score_year - 3 WHERE NOT EXISTS ( SELECT 1 FROM student_skill_score b1 WHERE b1.student_name = cur.student_name AND b1.skill_category = cur.skill_category AND b1.score_year = cur.score_year - 1 ) AND NOT EXISTS ( SELECT 1 FROM student_skill_score b2 WHERE b2.student_name = cur.student_name AND b2.skill_category = cur.skill_category AND b2.score_year = cur.score_year - 2 ) ) t3 ORDER BY growth_val DESC LIMIT 1;
方案说明
- 不需要手动拼接OR条件实现优先级:通过给不同间隔打优先级标签,再按分组取最优值的方式,就能自动保证优先级逻辑
- 拆UNION是兼容老数据库的可选方案,而非唯一方案;新数据库优先用窗口函数版,代码更易维护
内容的提问来源于stack exchange,提问作者Nick Adams
相关产品推荐
相关产品推荐

