MySQL中如何基于COUNT(DISTINCT)与GROUP BY实现反向分组统计？

嘿，这个问题我之前也碰到过！你没法直接用unique_shops分组的原因很简单：MySQL在执行GROUP BY逻辑的时候，SELECT子句里的别名还没被解析，直接引用就会报错。不过完全不用额外加列，用子查询或者CTE就能轻松搞定～

方案一：子查询（兼容所有MySQL版本） #

先通过子查询算出每位访客的不同店铺访问数，再基于这个临时结果集做二次分组统计：

SELECT 
    unique_shops AS Shops, 
    COUNT(Visitor) AS Visitors
FROM (
    -- 先执行你原来的查询，得到每个访客的unique_shops
    SELECT 
        Visitor, 
        COUNT(DISTINCT Shop) AS unique_shops 
    FROM my_table 
    GROUP BY Visitor
) AS temp_result  -- 给子查询起个临时表名
GROUP BY unique_shops
ORDER BY Shops DESC;

方案二：CTE（MySQL 8.0+ 支持） #

如果你的MySQL版本是8.0及以上，用CTE（公共表表达式）会让代码逻辑更清晰，拆分更直观：

-- 先定义一个CTE，存储每个访客的店铺访问数
WITH visitor_shop_stats AS (
    SELECT 
        Visitor, 
        COUNT(DISTINCT Shop) AS unique_shops 
    FROM my_table 
    GROUP BY Visitor
)
-- 再对CTE的结果做分组统计
SELECT 
    unique_shops AS Shops, 
    COUNT(Visitor) AS Visitors
FROM visitor_shop_stats
GROUP BY unique_shops
ORDER BY Shops DESC;

结果验证 #

基于你提供的数据，这两种写法都会得到你期望的结果：
+---------+---------+
| Shops | Visitors|
+---------+---------+
| 3 | 1 |
| 2 | 2 |
+---------+---------+

（如果需要和你示例里的顺序完全一致，把ORDER BY Shops DESC改成ORDER BY Shops ASC就行）

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