MySQL如何按国家统计各版本对应的用户最高版本分布数量

正确实现方案 #

静态版本（版本固定为V1~V6时使用） #

SELECT 
    t1.COUNTRY AS Country,
    SUM(CASE WHEN max_ver = 1 THEN 1 ELSE 0 END) AS V1,
    SUM(CASE WHEN max_ver = 2 THEN 1 ELSE 0 END) AS V2,
    SUM(CASE WHEN max_ver = 3 THEN 1 ELSE 0 END) AS V3,
    SUM(CASE WHEN max_ver = 4 THEN 1 ELSE 0 END) AS V4,
    SUM(CASE WHEN max_ver = 5 THEN 1 ELSE 0 END) AS V5,
    SUM(CASE WHEN max_ver = 6 THEN 1 ELSE 0 END) AS V6
FROM Table1 t1
INNER JOIN (
    -- 先统计每个用户的最高版本，确保每个用户仅对应一条记录
    SELECT USER, MAX(VERSION) AS max_ver
    FROM Table2
    GROUP BY USER
) t2 ON t1.USER = t2.USER
GROUP BY t1.COUNTRY;

逻辑说明 #

1. 内层子查询先按用户分组，计算出每个用户的最高版本号，从根源避免低版本数据干扰统计
2. 关联用户表拿到用户所属国家
3. 外层按国家分组，通过CASE条件判断每个用户的最高版本归属列，用SUM累加计数，完成行转列统计

动态版本（版本数量不固定时使用） #

如果后续会新增版本号，不需要手动修改SQL，可使用动态SQL自动适配所有版本：

SET @sql = NULL;
-- 自动生成所有版本对应的统计语句
SELECT GROUP_CONCAT(DISTINCT
    CONCAT('SUM(CASE WHEN max_ver = ', VERSION, ' THEN 1 ELSE 0 END) AS V', VERSION)
) INTO @sql
FROM Table2;

SET @sql = CONCAT('SELECT t1.COUNTRY AS Country, ', @sql, ' 
FROM Table1 t1
INNER JOIN (
    SELECT USER, MAX(VERSION) AS max_ver
    FROM Table2
    GROUP BY USER
) t2 ON t1.USER = t2.USER
GROUP BY t1.COUNTRY');

-- 执行动态生成的SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

原有写法问题说明 #

• 第一种写法直接嵌套使用COUNT(MAX(...))不符合SQL语法规范，聚合函数不能直接嵌套调用，且缺少行转列逻辑
• 第二种写法逻辑冗余，子查询的版本判断条件错误，无法准确匹配用户的最高版本，导致统计结果异常

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