如何查询含缺失cl_desc字段的表列并保留全量统计结果

问题 #

现有三张表prem_pay、sched、clist，需要查询得到reason（对应cl_val）、cl_desc、count三列，要求：

• 保留cl_desc为空的记录，同时展示对应reason和统计计数
• prem_pay的reason列需匹配clist的cl_val列（匹配规则为SUBSTRING(c.cl_val, 0, 10) = p.reason）
• sched仅用于日期和关联条件过滤

首次尝试的SQL：

SELECT c.cl_val, c.cl_desc, 
    (SELECT count(*) FROM prem_pay p, sched s
    WHERE p.shiftdate >= '05-Nov-2023' AND p.shiftdate <= '02-Dec-2023'
    AND s.cost_unit = 'TRAIN' 
    AND p.id=s.id AND p.shiftdate=s.shiftdate AND p.shiftstart=s.shiftstart
    AND SUBSTRING(c.cl_val, 0, 10) = p.reason) 
FROM clist c where cl_name = 'PREMIUM_REASON';

该查询能正常返回计数，但无法获取cl_desc缺失的记录（即prem_pay中存在但clist里无匹配的reason）。

改用LEFT JOIN的SQL：

SELECT p.reason, c.cl_desc, count(*)
FROM sched s, prem_pay p
LEFT JOIN clist c
  ON c.cl_name = 'PREMIUM_REASON' AND SUBSTRING(c.cl_val, 0, 10) = p.reason 
WHERE s.shiftdate >= '08-Oct-2023' AND s.shiftdate <= '04-Nov-2023'
AND s.cost_unit = 'TRAIN'
AND p.id=s.id AND p.shiftdate=s.shiftdate AND p.shiftstart=s.shiftstart 
GROUP BY 1,2

该查询能获取cl_desc缺失的记录，但丢失了clist中计数为0的行。

期望结果需包含：

• clist表中cl_name='PREMIUM_REASON'的全量行（即使计数为0）
• prem_pay中存在但clist无匹配的reason记录（cl_desc为空，展示实际计数）

示例期望结果：

|cl_val              |cl_desc                      |col3         |
+--------------------+-----------------------------+-------------+
|AD                  |Admission                    |            0|
|COVID-19            |COVID-19                     |            0|
|DC                  |Documentation                |            0|
|EW                  |Extreme Weather              |            2|
|HC                  |High Census/Acuity           |            0|
|HOL                 |Holiday                      |            0|
|ME                  |Mandatory Education          |            0|
|OP                  |Open Positions               |            0|
|SELECT              |SELECT A REASON              |            0|
|SELECT A REASON     |SELECT A REASON              |            0|
|SN                  |Sitter Needed                |            0|
|UL                  |Unplanned Leave              |            0|
|UP                  |Unpredicted Patient Care     |            0|
|LC                  |                             |            1|

解决方案 #

要同时满足两个需求，需以clist为基础保留全量行，再通过UNION ALL补充prem_pay中未匹配到clist的记录，具体SQL如下：

-- 第一部分：获取clist全量行及对应计数
SELECT 
    c.cl_val AS reason,
    c.cl_desc,
    COALESCE(p.count_num, 0) AS count
FROM clist c
LEFT JOIN (
    -- 预统计符合条件的prem_pay记录，按匹配规则分组
    SELECT 
        SUBSTRING(p.reason, 0, 10) AS match_reason,
        COUNT(*) AS count_num
    FROM prem_pay p
    JOIN sched s 
        ON p.id = s.id 
        AND p.shiftdate = s.shiftdate 
        AND p.shiftstart = s.shiftstart
    WHERE 
        s.shiftdate >= '08-Oct-2023' 
        AND s.shiftdate <= '04-Nov-2023'
        AND s.cost_unit = 'TRAIN'
    GROUP BY SUBSTRING(p.reason, 0, 10)
) p ON SUBSTRING(c.cl_val, 0, 10) = p.match_reason
WHERE c.cl_name = 'PREMIUM_REASON'

UNION ALL

-- 第二部分：获取prem_pay中未匹配到clist的记录（cl_desc为空）
SELECT 
    p.reason,
    NULL AS cl_desc,
    COUNT(*) AS count
FROM prem_pay p
JOIN sched s 
    ON p.id = s.id 
    AND p.shiftdate = s.shiftdate 
    AND p.shiftstart = s.shiftstart
WHERE 
    s.shiftdate >= '08-Oct-2023' 
    AND s.shiftdate <= '04-Nov-2023'
    AND s.cost_unit = 'TRAIN'
    -- 筛选clist中不存在的reason
    AND NOT EXISTS (
        SELECT 1 
        FROM clist c 
        WHERE c.cl_name = 'PREMIUM_REASON' 
        AND SUBSTRING(c.cl_val, 0, 10) = SUBSTRING(p.reason, 0, 10)
    )
GROUP BY p.reason;

逻辑说明 #

1. 第一部分：以clist作为左表，关联预统计好的prem_pay计数结果，用COALESCE将NULL计数转为0，确保clist全量行（包括计数为0的）都被保留。
2. 第二部分：通过NOT EXISTS过滤出prem_pay中未匹配到clist的reason记录，cl_desc设为NULL，统计其实际计数后，用UNION ALL与第一部分结果合并。
3. 将sched与prem_pay的关联放在子查询中预统计，避免主查询关联导致的过滤问题；统一使用SUBSTRING截取前10位的匹配规则，保证逻辑一致性。

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