如何用SQL按子类型数量统计不同分组的账户数量

我有一张存储账户信息的表，每个账户的唯一子类型数量为1至6种。目前表中仅通过Single_V_Multi字段区分单/多子类型，但无法统计不同子类型数量对应的账户总数（比如2种子类型的账户数、3种子类型的账户数等）。由于账户数量庞大无法手动处理，需要纯SQL方案实现按子类型数量统计账户分布。

示例数据 #

| account   | Sub-Type  | Single_V_Multi |
|-----------|-----------|----------------|
|123456789  |123456789  | Multi          |
|123456789  |123456790  | Multi          | 
|123456789  |123456791  | Multi          |
|123456792  |123456792  | Single         |
|123456793  |123456793  | Multi          |
|123456793  |123456794  | Multi          |
|123456795  |123456795  | Single         |
|123456796  |123456796  | Single         |
|123456797  |123456797  | Single         |
|123456798  |123456798  | Single         |
|123456799  |123456799  | Multi          |
|123456799  |123456800  | Multi          |
|123456799  |123456801  | Multi          |
|123456799  |123456802  | Multi          |

已完成的查询 #

我已经写出统计每个账户子类型数量的SQL：

SELECT account, COUNT(DISTINCT `Sub-Type`) as BAN_SUB_COUNT
FROM `Table`
GROUP BY account;

该查询输出结果：

| account   | BAN_SUB_COUNT |
|-----------|---------------|
|123456789  | 3             |
|123456792  | 1             |
|123456793  | 2             |
|123456795  | 1             |
|123456796  | 1             |
|123456797  | 1             |
|123456798  | 1             |
|123456799  | 4             |

期望输出 #

需要基于上述结果，统计每个BAN_SUB_COUNT对应的账户数量，理想输出如下：

| BAN_SUB_COUNT | count of Accounts |
|---------------|-------------------|
| 1             | 5                 |
| 2             | 1                 |
| 3             | 1                 |
| 4             | 1                 |

解决方案 #

可以通过嵌套查询或者CTE（公共表表达式）实现需求，两种方式都能高效处理大数据量场景：

方法1：嵌套查询 #

直接将已有的统计结果作为子查询，在外层对BAN_SUB_COUNT分组计数：

SELECT 
    BAN_SUB_COUNT,
    COUNT(account) as `count of Accounts`
FROM (
    SELECT 
        account, 
        COUNT(DISTINCT `Sub-Type`) as BAN_SUB_COUNT
    FROM `Table`
    GROUP BY account
) AS account_sub_counts
GROUP BY BAN_SUB_COUNT
ORDER BY BAN_SUB_COUNT;

方法2：CTE（更易读） #

如果你的SQL环境支持CTE（如MySQL 8.0+、PostgreSQL、SQL Server等），可以用更清晰的写法：

WITH account_sub_counts AS (
    SELECT 
        account, 
        COUNT(DISTINCT `Sub-Type`) as BAN_SUB_COUNT
    FROM `Table`
    GROUP BY account
)
SELECT 
    BAN_SUB_COUNT,
    COUNT(account) as `count of Accounts`
FROM account_sub_counts
GROUP BY BAN_SUB_COUNT
ORDER BY BAN_SUB_COUNT;

核心逻辑说明 #

1. 内层查询先按账户分组，统计每个账户的唯一子类型数量；
2. 外层查询再按子类型数量分组，统计对应的账户个数；
3. 加上ORDER BY BAN_SUB_COUNT可让结果按子类型数量升序排列，更直观；
4. 字段名包含特殊字符（如Sub-Type、count of Accounts）时，需用反引号（`）包裹，避免语法错误。

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