You need to enable JavaScript to run this app. 如何在SQL自连接条件中实现if-then逻辑计算学生技能得分增长
优惠活动
大模型
产品
解决方案
定价
更多

如何在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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.02 19:54:01