PostgreSQL关联查询问题：按状态分组统计短信关联记录

嘿，这个问题我太熟了！你遇到的是典型的「缺失维度值导致统计结果不完整」的问题，核心解法就是先把所有需要统计的msg_status（0-4）都列出来，再和你的业务表做关联，这样哪怕某个状态没有对应记录，也能返回0而不是空值。

要保证每个msg_status（0-4）都能被统计到，哪怕没有匹配的记录，关键是先构造出包含所有目标状态的完整列表，以此作为统计的“基准维度”，再和你的两张业务表做左连接，最后用聚合函数把空值转换成0。

具体实现步骤 #

1. 生成完整的状态值集合 #

首先需要生成一个包含0到4所有整数的数据集，这样我们就能覆盖所有可能的状态值。用VALUES子句是最直接的方式：

SELECT status AS msg_status
FROM (VALUES (0), (1), (2), (3), (4)) AS all_status(status)

如果你的数据库不支持VALUES（比如老版本MySQL），可以用UNION ALL来构造：

SELECT 0 AS msg_status UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4

2. 关联业务表并统计次数 #

接下来把这个完整状态列表和你的短信表做交叉连接（让每条短信都对应所有5个状态），再左连接到短信消息表，最后分组统计：

基础版（按短信+状态分行统计）

SELECT
    s.sms_pk,
    s.sms_title,
    all_status.msg_status,
    -- COUNT只统计非NULL值，没有匹配记录时会返回0
    COUNT(m.msg_pk) AS status_count
FROM svc_sms s
-- 交叉连接：让每条短信都和5个状态一一配对
CROSS JOIN (VALUES (0), (1), (2), (3), (4)) AS all_status(msg_status)
-- 左连接：匹配对应短信+状态的消息记录，没有则返回NULL
LEFT JOIN svc_sms_msg m 
    ON s.sms_pk = m.sms_pk 
    AND all_status.msg_status = m.msg_status
-- 按短信和状态分组
GROUP BY s.sms_pk, s.sms_title, all_status.msg_status
ORDER BY s.sms_pk, all_status.msg_status;

行转列版（每个状态的统计结果在同一行）

如果希望把每个状态的统计值放在同一行展示（更直观），可以用SUM(CASE...)的写法：

SELECT
    s.sms_pk,
    s.sms_title,
    SUM(CASE WHEN m.msg_status = 0 THEN 1 ELSE 0 END) AS status_0_count,
    SUM(CASE WHEN m.msg_status = 1 THEN 1 ELSE 0 END) AS status_1_count,
    SUM(CASE WHEN m.msg_status = 2 THEN 1 ELSE 0 END) AS status_2_count,
    SUM(CASE WHEN m.msg_status = 3 THEN 1 ELSE 0 END) AS status_3_count,
    SUM(CASE WHEN m.msg_status = 4 THEN 1 ELSE 0 END) AS status_4_count
FROM svc_sms s
LEFT JOIN svc_sms_msg m ON s.sms_pk = m.sms_pk
GROUP BY s.sms_pk, s.sms_title
ORDER BY s.sms_pk;

关键细节解释 #

• CROSS JOIN的作用：确保每条短信都和所有5个状态配对，这样后续左连接时不会漏掉任何状态的统计项。
• LEFT JOIN的必要性：即使某个短信在某个状态下没有对应的消息记录，也会保留这条配对记录，此时m.msg_pk为NULL，COUNT(m.msg_pk)会自动返回0（因为COUNT不统计NULL值）。
• 如果用SUM(CASE...)，当没有对应记录时，CASE表达式会返回0，SUM的结果自然也是0，完美解决空值问题。

这样不管有没有对应记录，每个短信的每个状态都会有统计值，再也不会出现空值啦！

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