MySQL查询：关联多表时返回指定类型行及默认值问题

原查询的问题在于以absences表作为主表，导致没有对应缺勤记录的类型被自动过滤。要实现所有指定缺勤类型都返回，缺失记录用absence_type默认值填充的需求，需要把目标缺勤类型集合作为主数据集，再通过LEFT JOIN关联缺勤记录和余额表。

示例修正查询（适配常见表结构） #

假设你要查询员工ID为123、年度为2024、指定缺勤类型ID为(1,2,3)的数据，修正后的SQL如下：

SELECT
  at.id AS absence_type_id,
  at.type_name,
  -- 无缺勤记录时，用0或absence_type的默认已用值（按需调整）
  COALESCE(a.days_taken, 0) AS days_taken,
  -- 无余额记录时，用absence_type的默认余额值
  COALESCE(ab.balance, at.default_balance) AS remaining_balance
FROM
  -- 主数据集：筛选出所有需要查询的缺勤类型
  (SELECT id, type_name, default_balance FROM absence_type WHERE id IN (1,2,3)) AS at
-- LEFT JOIN缺勤记录表，关联类型ID、员工ID、年度
LEFT JOIN absences a
  ON at.id = a.absence_type_id
  AND a.employee_id = 123
  AND a.year = 2024
-- LEFT JOIN余额记录表，关联类型ID、员工ID、年度
LEFT JOIN absence_balance ab
  ON at.id = ab.absence_type_id
  AND ab.employee_id = 123
  AND ab.year = 2024
ORDER BY at.id;

关键调整说明 #

1. 主表切换：将筛选后的absence_type作为主表，确保所有指定的缺勤类型都会被返回，无论是否有对应记录。
2. 关联条件位置：把employee_id和year的过滤条件放在ON子句而非WHERE子句中——如果放在WHERE，会过滤掉LEFT JOIN产生的NULL行，等同于INNER JOIN的效果。
3. 默认值填充：用COALESCE函数判断，当absences或absence_balance无匹配记录时，自动替换为absence_type中的默认字段值（需根据实际表字段名调整，比如default_days等）。

按需调整点 #

• 如果需要通过计算得到余额（比如默认天数 - 已用天数），可以修改余额字段的表达式：

  COALESCE(ab.balance, at.default_days - COALESCE(a.days_taken, 0)) AS remaining_balance
  

• 若使用参数化查询（比如应用程序传入类型集合），将IN (1,2,3)替换为对应参数（如IN (:type_ids)或@type_ids，视数据库类型而定）。

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