无GROUP BY子句时SQL获取MAX(RunNumber)异常问题求助

你的SQL语句存在两个核心问题：

1. 子查询未包含RunNumber字段，外层查询的MAX(RunNumber)没有有效数据来源，因此返回默认值0
2. 若要基于RunNumber（数据追加标识）做针对性统计，原语句缺少对应逻辑支撑

修正方案1：按每个RunNumber统计空值数量 #

如果需要查看每一批次（不同RunNumber）的字段空值统计，需在子查询中保留RunNumber，并在外层按其分组：

SELECT
    RunNumber,
    CAST(SUM(FirstName) AS INT) AS FirstName_Null_Count,
    CAST(SUM(LastName) AS INT) AS LastName_Null_Count,
    CAST(SUM(State) AS INT) AS State_Null_Count
FROM
    (SELECT
         RunNumber,
         CASE WHEN FirstName IS NULL THEN 1 ELSE 0 END FirstName,
         CASE WHEN LastName IS NULL THEN 1 ELSE 0 END LastName,
         CASE WHEN State IS NULL THEN 1 ELSE 0 END State
     FROM 
         Table.Customers (nolock)
     WHERE 
         DateOfRecord = '5/1/2023') a
GROUP BY RunNumber;

修正方案2：仅统计最新追加批次的空值数量 #

如果只需要查看最新批次（最大RunNumber）的空值统计，可直接筛选目标批次后计算：

SELECT
    MAX(RunNumber) AS Latest_RunNumber,
    CAST(SUM(CASE WHEN FirstName IS NULL THEN 1 ELSE 0 END) AS INT) AS FirstName_Null_Count,
    CAST(SUM(CASE WHEN LastName IS NULL THEN 1 ELSE 0 END) AS INT) AS LastName_Null_Count,
    CAST(SUM(CASE WHEN State IS NULL THEN 1 ELSE 0 END) AS INT) AS State_Null_Count
FROM 
    Table.Customers (nolock)
WHERE 
    DateOfRecord = '5/1/2023'
    AND RunNumber = (SELECT MAX(RunNumber) FROM Table.Customers (nolock) WHERE DateOfRecord = '5/1/2023');

输出说明 #

• 方案1返回所有RunNumber批次对应的各字段空值数量
• 方案2仅返回最新RunNumber批次的统计结果，同时解决原查询中RunNumber显示0的问题

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