SQL中使用MAX函数查询日期时，不存在的日期如何返回'0'

需求说明 #

需要返回全量设备对应的最近一次检查日期，从未产生过检查记录的设备也需要保留在结果中，无记录的设备日期显示为0。
示例源数据：

device number    date inspected
-------------------------------
     01          2015-01-01
     01          2021-03-05
     02 
     03          2022-01-01

预期输出：

01    2021-03-05
02    0 
03    2022-01-01

现有代码问题 #

你当前编写的SQL存在3个缺失点，无法得到正确结果：

• 未做表关联：如果检查记录存储在独立的巡检记录表中，需要和存储全量设备的主表做左连接，才能保留无检查记录的设备
• 缺少分组逻辑：使用MAX()聚合函数时，必须搭配GROUP BY按设备编号分组，才能按单台设备维度计算最大日期
• 未做空值转换：无检查记录的设备关联后日期字段为NULL，需要转换为0才能匹配预期输出

正确SQL代码 #

假设存储全量设备的主表为dbo.DEVICE，字段NUMBER为设备编号；检查记录存储在dbo.INSPECTION表，关联字段为设备编号，检查日期字段为DATE_INSPECTED，你可以根据实际表结构替换对应名称：

SELECT
    d.NUMBER AS [device number],
    ISNULL(CAST(MAX(i.DATE_INSPECTED) AS VARCHAR(20)), '0') AS [date inspected]
FROM dbo.DEVICE d
-- 左连接检查记录表，保留主表中所有设备
LEFT JOIN dbo.INSPECTION i
    ON d.NUMBER = i.DEVICE_NUMBER
-- 按设备编号分组聚合，计算每台设备的最新检查日期
GROUP BY d.NUMBER

逻辑说明 #

• LEFT JOIN以设备主表为左表关联检查记录，确保无检查记录的设备不会被过滤掉
• GROUP BY d.NUMBER配合MAX(i.DATE_INSPECTED)，逐台设备取最大（即最近）的检查日期
• ISNULL函数判断聚合后的日期值，如果为NULL（即该设备无任何检查记录）则返回0；如果你的检查日期字段本身是字符串类型，可以去掉CAST类型转换逻辑，直接写ISNULL(MAX(i.DATE_INSPECTED), '0')即可

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