mysql - Union All 返回表中没有记录的值

Question

我想根据相关表的值分别显示重新提交和首次提交(两者都是声明类型)。

所以我使用下面的查询来分别获取这两个记录。

SELECT ROUND(coalesce(SUM(c.ClaimNet), 0), 2) as net, 
       MAX(c.ClaimID), count(c.ClaimID) as claims, 
       MAX(h.TransactionDate) as TransactionDate, 
       'Resubmission' AS 'Claim Type' 
FROM Claim c 
LEFT OUTER JOIN ClaimHeader h on h.HeaderID = c.HeaderPKID 
INNER JOIN Resubmission r ON r.ClaimID = c.ClaimPKID WHERE h.HeaderType=2 

UNION ALL 

SELECT ROUND(coalesce(SUM(c.ClaimNet), 0), 2) as net, 
       MAX(c.ClaimID), count(c.ClaimID) as claims, 
       MAX(h.TransactionDate) as TransactionDate, 
      'First Submission' AS 'Claim Type' 
FROM Claim c 
LEFT OUTER JOIN ClaimHeader h on h.HeaderID = c.HeaderPKID 
WHERE ClaimPKID NOT IN 
         ( SELECT ClaimID FROM Resubmission GROUP BY ClaimID ) AND HeaderType=2

在上面的查询中，我使用了UNION ALL从Claim表中获取数据。如果声明表主键用作重新提交表中的外键，则它是重新提交声明类型，如果未在重新提交表中使用，则它是首次提交。

我的问题是，尽管表中没有特定选择查询的记录，但它会返回空值并提及声明类型。请引用下面的截图。

Answer 1

试试这个:

select * 
from
(SELECT ROUND(coalesce(SUM(c.ClaimNet), 0), 2) as net, 
       MAX(c.ClaimID) claimid, count(c.ClaimID) as claims, 
       MAX(h.TransactionDate) as TransactionDate, 
       'Resubmission' AS 'Claim Type' 
FROM Claim c 
LEFT OUTER JOIN ClaimHeader h on h.HeaderID = c.HeaderPKID 
INNER JOIN Resubmission r ON r.ClaimID = c.ClaimPKID WHERE h.HeaderType=2 

UNION ALL 

SELECT ROUND(coalesce(SUM(c.ClaimNet), 0), 2) as net, 
       MAX(c.ClaimID), count(c.ClaimID) as claims, 
       MAX(h.TransactionDate) as TransactionDate, 
      'First Submission' AS 'Claim Type' 
FROM Claim c 
LEFT OUTER JOIN ClaimHeader h on h.HeaderID = c.HeaderPKID 
WHERE ClaimPKID NOT IN 
         ( SELECT ClaimID FROM Resubmission GROUP BY ClaimID ) AND HeaderType=2) claims
where claims.claims <> 0