MySQL查询中多IF条件实现教师院系匹配薪资取值或置0方案

原有写法的核心问题 #

• 不能在SELECT列定义中使用存储过程专属的IF ... THEN流程控制语法，查询场景下要使用CASE WHEN表达式完成行级条件判断
• 查询结果的列别名是固定的，无法在运行时动态切换别名，需要为每个部门的薪资单独定义对应列，匹配条件显示薪资，不匹配显示0
• 原有逻辑存在笔误：部门2的冬季薪资别名错误写为部门1的别名

标准实现方案（静态列） #

该方案返回结果包含所有部门的薪资列，仅匹配对应部门ID的行显示真实薪资，其余行该列显示0，同时关联departments表获取部门信息：

SELECT
  -- 基础信息字段，可按需增减
  t.teacher_id,
  t.teacher_name,
  d.department_name,
  -- 部门1薪资列
  CASE WHEN t.teacher_department_id = 1 THEN t.teacher_summer_salary ELSE 0 END AS department_one_summer_salary,
  CASE WHEN t.teacher_department_id = 1 THEN t.teacher_winter_salary ELSE 0 END AS department_one_winter_salary,
  -- 部门2薪资列
  CASE WHEN t.teacher_department_id = 2 THEN t.teacher_summer_salary ELSE 0 END AS department_two_summer_salary,
  CASE WHEN t.teacher_department_id = 2 THEN t.teacher_winter_salary ELSE 0 END AS department_two_winter_salary,
  -- 部门3薪资列
  CASE WHEN t.teacher_department_id = 3 THEN t.teacher_summer_salary ELSE 0 END AS department_three_summer_salary,
  CASE WHEN t.teacher_department_id = 3 THEN t.teacher_winter_salary ELSE 0 END AS department_three_winter_salary
FROM teachers t
-- 关联部门表，使用LEFT JOIN保留无对应部门的教师数据，需过滤无部门数据可改为INNER JOIN
LEFT JOIN departments d ON t.teacher_department_id = d.department_id;

动态列实现方案（按需生成列） #

如果需要仅返回当前预设部门ID对应的薪资列，可使用MySQL预处理动态SQL实现：

-- 替换为你要匹配的预设部门ID
SET @target_department_id = 2;
SET @sql = CONCAT('
SELECT
  t.teacher_id,
  t.teacher_name,
  d.department_name,
  CASE WHEN t.teacher_department_id = ',@target_department_id,' THEN t.teacher_summer_salary ELSE 0 END AS department_',ELT(@target_department_id,'one','two','three'),'_summer_salary,
  CASE WHEN t.teacher_department_id = ',@target_department_id,' THEN t.teacher_winter_salary ELSE 0 END AS department_',ELT(@target_department_id,'one','two','three'),'_winter_salary
FROM teachers t
LEFT JOIN departments d ON t.teacher_department_id = d.department_id
');
-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

内容的提问来源于stack exchange，提问作者user15425589