如何查询包含未匹配Lookup表的TR_CODE的分组统计结果？

要解决内连接无法获取未匹配TR_CODE（如NA）的问题，核心是改用左连接保留用户表的所有目标记录，再通过空值处理函数补全缺失的描述：

基础实现（统计存在的分组） #

SELECT
    u.TR_CODE,
    -- 优先用查找表的描述，无匹配则用TR_CODE自身
    COALESCE(l.LK_DSC, u.TR_CODE) AS TR_DESCRIPTION,
    COUNT(u.U_ID) AS RECORD_COUNT
FROM
    `user` u
-- 左连接：保留用户表中STATUS为closed的所有记录，无论lookup表是否匹配
LEFT JOIN
    lookup l ON u.TR_CODE = l.LK_CD
WHERE
    u.STATUS = 'closed'
GROUP BY
    u.TR_CODE, COALESCE(l.LK_DSC, u.TR_CODE)
ORDER BY
    RECORD_COUNT DESC;

强制输出指定分组（含无数据的分组） #

如果需要确保Success、Cancelled、Invalid、Denied、NA这五个分组无论是否有数据都显示（无数据时数量为0），可以用虚拟表生成固定分组后再左连接：

WITH required_groups AS (
    SELECT 'Success' AS TR_CODE UNION ALL
    SELECT 'Cancelled' UNION ALL
    SELECT 'Invalid' UNION ALL
    SELECT 'Denied' UNION ALL
    SELECT 'NA'
)
SELECT
    rg.TR_CODE,
    COALESCE(l.LK_DSC, rg.TR_CODE) AS TR_DESCRIPTION,
    COUNT(u.U_ID) AS RECORD_COUNT
FROM
    required_groups rg
-- 先关联用户表，过滤STATUS为closed的记录
LEFT JOIN
    `user` u ON rg.TR_CODE = u.TR_CODE AND u.STATUS = 'closed'
-- 关联查找表获取描述
LEFT JOIN
    lookup l ON rg.TR_CODE = l.LK_CD
GROUP BY
    rg.TR_CODE, COALESCE(l.LK_DSC, rg.TR_CODE)
ORDER BY
    rg.TR_CODE;

关键说明 #

• LEFT JOIN：区别于内连接，会保留左表（用户表/固定分组表）的所有记录，右表无匹配时对应字段为NULL
• COALESCE函数：接收多个参数，返回第一个非NULL的值，完美解决无匹配描述时用TR_CODE补全的需求

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