DB2 UNION查询需求：无匹配记录时返回0值

这个需求我碰到过好多次，核心就是要确保每个指定的Range类别都能返回一行记录——哪怕该类别下没有匹配的数据，这时候计数就显示0。原查询的问题在于，当某个分支的WHERE条件没命中任何记录时，整个分支就不会输出行，导致结果缺失对应的Range项。

这里给你两种实用的解决思路，按需选择就行：

方法一：构造基础Range列表 + LEFT JOIN计数子查询 #

这种方法扩展性最好，先把所有需要的Range（PRIOR、Current、Full）固定下来，再分别关联每个Range对应的计数结果，最后用COALESCE把NULL转换成0。

示例SQL如下：

WITH required_ranges AS (
    SELECT 'PRIOR' AS Range UNION ALL
    SELECT 'Current' AS Range UNION ALL
    SELECT 'Full' AS Range
),
count_data AS (
    -- PRIOR分支的计数
    SELECT ID, 'PRIOR' AS Range, COUNT(*) AS count
    FROM table1
    WHERE ID = 123 AND date BETWEEN '2017-01-01' AND '2017-06-30'
    UNION ALL
    -- Current分支的计数
    SELECT ID, 'Current' AS Range, COUNT(*) AS count
    FROM table1
    WHERE ID = 123 AND date BETWEEN '2017-07-01' AND '2017-12-31'
    UNION ALL
    -- Full分支的计数
    SELECT ID, 'Full' AS Range, COUNT(*) AS count
    FROM table1
    WHERE ID = 123 AND date BETWEEN '2017-01-01' AND '2017-12-31'
)
SELECT 
    123 AS ID, -- 如果需要动态ID，可改成关联ID列表的方式
    rr.Range,
    COALESCE(cd.count, 0) AS count
FROM required_ranges rr
LEFT JOIN count_data cd ON rr.Range = cd.Range AND cd.ID = 123;

方法二：每个分支单独处理NULL情况 #

如果不想用CTE（公共表表达式），也可以给每个分支单独兜底：用空表生成默认行，再通过NOT EXISTS判断是否有实际数据，没有就返回0。

以MySQL为例，SQL代码如下：

-- PRIOR分支：有数据返回实际计数，无数据返回0
SELECT ID, 'PRIOR' AS Range, COUNT(*) AS count
FROM table1
WHERE ID = 123 AND date BETWEEN '2017-01-01' AND '2017-06-30'
UNION ALL
SELECT 123 AS ID, 'PRIOR' AS Range, 0 AS count
FROM (VALUES ()) AS dummy
WHERE NOT EXISTS (
    SELECT 1 FROM table1 WHERE ID = 123 AND date BETWEEN '2017-01-01' AND '2017-06-30'
)
-- 同理处理Current分支
UNION ALL
SELECT ID, 'Current' AS Range, COUNT(*) AS count
FROM table1
WHERE ID = 123 AND date BETWEEN '2017-07-01' AND '2017-12-31'
UNION ALL
SELECT 123 AS ID, 'Current' AS Range, 0 AS count
FROM (VALUES ()) AS dummy
WHERE NOT EXISTS (
    SELECT 1 FROM table1 WHERE ID = 123 AND date BETWEEN '2017-07-01' AND '2017-12-31'
)
-- 同理处理Full分支
UNION ALL
SELECT ID, 'Full' AS Range, COUNT(*) AS count
FROM table1
WHERE ID = 123 AND date BETWEEN '2017-01-01' AND '2017-12-31'
UNION ALL
SELECT 123 AS ID, 'Full' AS Range, 0 AS count
FROM (VALUES ()) AS dummy
WHERE NOT EXISTS (
    SELECT 1 FROM table1 WHERE ID = 123 AND date BETWEEN '2017-01-01' AND '2017-12-31'
);

小提示 #

• 方法一更适合后续可能新增Range的场景，只需要在required_ranges里加一行就行，不用改其他逻辑。
• 如果要批量处理多个ID，方法一可以调整为把required_ranges和ID列表做笛卡尔积，再左连接计数数据，灵活性更高。

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