Union All返回无记录值及分展两种Claim类型的SQL技术问询

嘿，我明白你的需求了——你想分别统计并展示Resubmission和First Submission两类Claim的数据，但现在用Union All的时候，某类没有对应记录就不会返回行，对吧？我给你梳理个靠谱的解决方案，确保不管哪类有没有数据，都能显示对应的Claim类型和合理的默认值。

核心思路 #

问题根源在于Union All只会返回有匹配数据的行，所以我们得先明确列出所有需要展示的Claim类型，再把统计结果和这个类型集合做左关联，这样哪怕某类没数据，也能保留对应的行，用默认值填充空值。

完整SQL实现 #

WITH ClaimTypes AS (
    -- 先定义要展示的两类Claim，确保无论数据是否存在，这两行都会保留
    SELECT 'Resubmission' AS ClaimType
    UNION ALL
    SELECT 'First Submission' AS ClaimType
),
-- 单独统计Resubmission类型的Claim数据
ResubmissionStats AS (
    SELECT 
        ROUND(COALESCE(SUM(c.ClaimNet), 0), 2) AS net,
        MAX(c.ClaimID) AS max_claim_id,
        COUNT(c.ClaimID) AS claims,
        MAX(h.TransactionDate) AS latest_transaction_date,
        'Resubmission' AS ClaimType
    FROM Claim c
    LEFT JOIN ClaimHeader h ON h.HeaderID = c.HeaderPKID
    -- 关联Resubmission表筛选重提交的Claim
    INNER JOIN Resubmission r ON r.ClaimID = c.ClaimPKID
    -- 替换成你原WHERE子句里剩余的条件，比如h.HeaderStatus = 'Completed'
    WHERE h.[你的剩余查询条件] = '对应值'
),
-- 统计First Submission类型的Claim（即未关联Resubmission表的Claim）
FirstSubmissionStats AS (
    SELECT 
        ROUND(COALESCE(SUM(c.ClaimNet), 0), 2) AS net,
        MAX(c.ClaimID) AS max_claim_id,
        COUNT(c.ClaimID) AS claims,
        MAX(h.TransactionDate) AS latest_transaction_date,
        'First Submission' AS ClaimType
    FROM Claim c
    LEFT JOIN ClaimHeader h ON h.HeaderID = c.HeaderPKID
    -- 用左关联+NULL判断筛选首次提交的Claim
    LEFT JOIN Resubmission r ON r.ClaimID = c.ClaimPKID
    WHERE r.ClaimID IS NULL
      -- 同样替换成你原WHERE子句的剩余条件
      AND h.[你的剩余查询条件] = '对应值'
)
-- 将类型集合与两个统计结果关联，确保所有类型都能显示
SELECT 
    ct.ClaimType,
    -- 用COALESCE把空值转为0，符合业务逻辑
    COALESCE(CASE WHEN ct.ClaimType = 'Resubmission' THEN rs.net ELSE fs.net END, 0) AS net,
    COALESCE(CASE WHEN ct.ClaimType = 'Resubmission' THEN rs.max_claim_id ELSE fs.max_claim_id END, 0) AS max_claim_id,
    COALESCE(CASE WHEN ct.ClaimType = 'Resubmission' THEN rs.claims ELSE fs.claims END, 0) AS claims,
    -- 日期无数据时可保留NULL，或改成你需要的默认日期（比如'1900-01-01'）
    CASE WHEN ct.ClaimType = 'Resubmission' THEN rs.latest_transaction_date ELSE fs.latest_transaction_date END AS latest_transaction_date
FROM ClaimTypes ct
LEFT JOIN ResubmissionStats rs ON ct.ClaimType = rs.ClaimType
LEFT JOIN FirstSubmissionStats fs ON ct.ClaimType = fs.ClaimType;

简化版方案（不用CTE也能搞定） #

如果觉得CTE有点复杂，也可以直接把两个统计用Union All合并，再和预定义的类型集合左关联：

WITH ClaimTypes AS (
    SELECT 'Resubmission' AS ClaimType
    UNION ALL
    SELECT 'First Submission' AS ClaimType
)
SELECT 
    ct.ClaimType,
    COALESCE(s.net, 0) AS net,
    COALESCE(s.max_claim_id, 0) AS max_claim_id,
    COALESCE(s.claims, 0) AS claims,
    s.latest_transaction_date
FROM ClaimTypes ct
LEFT JOIN (
    -- 重提交统计
    SELECT 
        ROUND(COALESCE(SUM(c.ClaimNet), 0), 2) AS net,
        MAX(c.ClaimID) AS max_claim_id,
        COUNT(c.ClaimID) AS claims,
        MAX(h.TransactionDate) AS latest_transaction_date,
        'Resubmission' AS ClaimType
    FROM Claim c
    LEFT JOIN ClaimHeader h ON h.HeaderID = c.HeaderPKID
    INNER JOIN Resubmission r ON r.ClaimID = c.ClaimPKID
    WHERE h.[你的剩余查询条件] = '对应值'
    UNION ALL
    -- 首次提交统计
    SELECT 
        ROUND(COALESCE(SUM(c.ClaimNet), 0), 2) AS net,
        MAX(c.ClaimID) AS max_claim_id,
        COUNT(c.ClaimID) AS claims,
        MAX(h.TransactionDate) AS latest_transaction_date,
        'First Submission' AS ClaimType
    FROM Claim c
    LEFT JOIN ClaimHeader h ON h.HeaderID = c.HeaderPKID
    LEFT JOIN Resubmission r ON r.ClaimID = c.ClaimPKID
    WHERE r.ClaimID IS NULL
      AND h.[你的剩余查询条件] = '对应值'
) s ON ct.ClaimType = s.ClaimType;

几个关键注意点 #

1. 记得把代码里的[你的剩余查询条件]替换成你原WHERE子句中没写完的部分，比如h.HeaderStatus = 'Approved'之类的。
2. 如果ClaimID是字符串类型，max_claim_id的默认值别用0，改成空字符串''更合理。
3. 日期字段latest_transaction_date如果没数据，你可以保留NULL，或者用COALESCE(s.latest_transaction_date, '1900-01-01')设置成业务需要的默认日期。

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