使用GREATEST/MAX/COALESCE处理处方表时无记录仍返回null的问题排查

问题根源 #

你遇到的问题核心是聚合函数MAX()的特性：即使WHERE条件没有匹配到任何记录，不带GROUP BY的聚合查询会把空数据集视为一个分组，返回一行全为NULL的结果——聚合函数结果为NULL，非聚合字段（如id、patient_id）因无匹配行也返回NULL。添加AND id IS NOT NULL AND patient_id IS NOT null无法解决，因为空数据集里根本没有这些字段的有效内容，聚合查询依然会生成空行。

此外你的查询还有一个潜在问题：COALESCE(MAX(suspended), 0)中用数字0和datetime类型的MAX(start_on)传入GREATEST()，会触发类型转换，可能导致日期计算错误，应该使用同类型的最小日期值（如'1970-01-01 00:00:00'）。

解决方案 #

方案1：添加HAVING过滤空聚合结果 #

通过HAVING子句过滤掉聚合后无有效数据的情况，确保只有存在匹配记录时才返回结果：

SELECT
  MAX(DrugsPrescriptions.id) AS DrugsPrescriptions__id,
  DrugsPrescriptions.patient_id AS DrugsPrescriptions__patient_id,
  GREATEST(
    MAX(`start_on`),
    COALESCE(
      MAX(`suspended`),
      '1970-01-01 00:00:00'
    )
  ) AS start_on
FROM
  drugs_prescriptions DrugsPrescriptions
WHERE
  (
    start_on <= '2025-03-05 15:44:36'
    AND patient_id = 49
  )
HAVING MAX(start_on) IS NOT NULL

注：用MAX(id)获取该患者的最大处方ID，避免非聚合字段未在GROUP BY中出现的SQL模式错误。

方案2：使用GROUP BY明确分组 #

针对单个患者的需求，用GROUP BY patient_id分组，当无匹配记录时，分组查询不会生成任何结果：

SELECT
  MAX(id) AS DrugsPrescriptions__id,
  patient_id AS DrugsPrescriptions__patient_id,
  GREATEST(
    MAX(start_on),
    COALESCE(MAX(suspended), '1970-01-01 00:00:00')
  ) AS start_on
FROM drugs_prescriptions
WHERE
  start_on <= '2025-03-05 15:44:36'
  AND patient_id = 49
GROUP BY patient_id

这个写法更规范，完全匹配你获取单个患者最后修改日期的需求。

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