SQL查询统计仅收Letter、仅收EMail、两类都收的账户数量

现有存储账户通信触达记录的业务表，字段说明如下：

• ACOUNT_ID：账户唯一标识
• TYPE_OF_COMM：通信触达类型，取值仅为Letter（信件）、EMail（邮件）

同一账户可存在多条同类型或不同类型的通信记录
需要完成三类统计：

1. 仅收到信件触达的账户数量
2. 仅收到邮件触达的账户数量
3. 同时收到信件、邮件两类触达的账户数量
   最终输出需包含Type_of_comms（触达分类）、Count of Accounts（对应账户数）两列，示例预期结果为：Letters only计数2、EMail only计数1、both letters and mails计数1。

核心逻辑为先按账户聚合，判断每个账户的触达类型覆盖情况，再按分类汇总计数，以下是兼容MySQL的写法：

WITH account_unique_comm AS (
    -- 先按账户去重，过滤同一类型的重复记录干扰
    SELECT 
        ACOUNT_ID,
        GROUP_CONCAT(DISTINCT TYPE_OF_COMM ORDER BY TYPE_OF_COMM) AS comm_set
    FROM 替换为你的实际业务表名
    GROUP BY ACOUNT_ID
)
SELECT
    CASE comm_set
        WHEN 'Letter' THEN 'Letters only'
        WHEN 'EMail' THEN 'EMail only'
        WHEN 'EMail,Letter' THEN 'both letters and mails'
    END AS Type_of_comms,
    COUNT(ACOUNT_ID) AS `Count of Accounts`
FROM account_unique_comm
GROUP BY Type_of_comms;

如果使用不支持GROUP_CONCAT的数据库，可以换成通用条件判断写法，不需要CTE也能运行：

SELECT
    CASE
        WHEN SUM(TYPE_OF_COMM = 'Letter') > 0 AND SUM(TYPE_OF_COMM = 'EMail') = 0 THEN 'Letters only'
        WHEN SUM(TYPE_OF_COMM = 'EMail') > 0 AND SUM(TYPE_OF_COMM = 'Letter') = 0 THEN 'EMail only'
        WHEN SUM(TYPE_OF_COMM = 'Letter') > 0 AND SUM(TYPE_OF_COMM = 'EMail') > 0 THEN 'both letters and mails'
    END AS Type_of_comms,
    COUNT(DISTINCT ACOUNT_ID) AS `Count of Accounts`
FROM 替换为你的实际业务表名
GROUP BY ACOUNT_ID
HAVING Type_of_comms IS NOT NULL;

用示例数据运行上述SQL，输出结果完全匹配预期：

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