如何对MySQL分组数据进行“解分组”并保留分组统计信息

背景 #

现有临时数据表temp_users，数据如下：

+--+-----+-----+--------+
|id|name |color|shape   |
+--+-----+-----+--------+
|1 |john |blue |square  |
|2 |mary |green|square  |
|3 |anna |red  |triangle|
|4 |bob  |blue |square  |
|5 |susan|blue |square  |
|6 |frank|red  |triangle|
+--+-----+-----+--------+

执行以下分组查询可按color和shape聚合，得到每组的用户列表和重复数量：

SELECT
    GROUP_CONCAT(name) AS names,
    color,
    shape,
    COUNT(*) AS nb_duplicates
FROM temp_users
GROUP BY color, shape;

查询结果：

+--------------+-----+--------+-------------+
|names         |color|shape   |nb_duplicates|
+--------------+-----+--------+-------------+
|john,bob,susan|blue |square  |3            |
|mary          |green|square  |1            |
|anna,frank    |red  |triangle|2            |
+--------------+-----+--------+-------------+

需求 #

需要将上述分组结果“解分组”，实现：

• 每条用户数据单独一行（至少保留id字段，其余字段可关联获取）
• 保留分组后的聚合信息：nb_duplicates（组内数据条数）和唯一的duplicate_group_id（自动递增的组ID）

预期输出：

+--+-----+-----+--------+-------------+------------------+
|id|name |color|shape   |nb_duplicates|duplicate_group_id|
+--+-----+-----+--------+-------------+------------------+
|1 |john |blue |square  |3            |1                 |
|2 |mary |green|square  |1            |2                 |
|3 |anna |red  |triangle|2            |3                 |
|4 |bob  |blue |square  |3            |1                 |
|5 |susan|blue |square  |3            |1                 |
|6 |frank|red  |triangle|2            |3                 |
+--+-----+-----+--------+-------------+------------------+

尝试过的方案 #

参考类似问题编写了以下SQL：

SELECT
    u.*,
    dups.nb_duplicates
FROM temp_users u
INNER JOIN (
    SELECT
        u2.color,
        u2.shape,
        COUNT(*) AS nb_duplicates
    FROM temp_users u2
    GROUP BY color, shape
) AS dups ON u.color = dups.color AND u.shape = dups.shape;

但MySQL报错：

[HY000][1137] Can't reopen table: 'u'

解决方案 #

方案1：使用窗口函数（MySQL 8.0+ 推荐） #

利用窗口函数可直接在原表上计算分组聚合信息，无需关联子查询，语法简洁高效：

SELECT
    id,
    name,
    color,
    shape,
    COUNT(*) OVER (PARTITION BY color, shape) AS nb_duplicates,
    DENSE_RANK() OVER (ORDER BY color, shape) AS duplicate_group_id
FROM temp_users
ORDER BY id;

• COUNT(*) OVER (PARTITION BY color, shape)：对每个color+shape分组计算总条数，即nb_duplicates
• DENSE_RANK() OVER (ORDER BY color, shape)：按color和shape排序生成唯一的组ID，自动递增

方案2：兼容低版本MySQL（无窗口函数） #

报错原因是MySQL临时表默认不允许在同一个查询中被多次引用，解决方法是先将分组结果存入另一个临时表，再关联查询：

-- 第一步：创建分组结果临时表
CREATE TEMPORARY TABLE temp_groups AS
SELECT
    color,
    shape,
    COUNT(*) AS nb_duplicates,
    @group_id := @group_id + 1 AS duplicate_group_id
FROM temp_users, (SELECT @group_id := 0) AS init
GROUP BY color, shape;

-- 第二步：关联原表和分组表
SELECT
    u.id,
    u.name,
    u.color,
    u.shape,
    g.nb_duplicates,
    g.duplicate_group_id
FROM temp_users u
JOIN temp_groups g ON u.color = g.color AND u.shape = g.shape
ORDER BY u.id;

• 通过变量@group_id生成递增的组ID，存入temp_groups临时表
• 将原表与temp_groups关联，获取每条数据对应的分组信息

表创建语句 #

快速复现测试环境的SQL：

DROP TABLE IF EXISTS temp_users;
CREATE TEMPORARY TABLE temp_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20),
    color VARCHAR(20),
    shape VARCHAR(20)
);
INSERT INTO temp_users(name, color, shape) VALUES
('john',  'blue',  'square'),
('mary',  'green', 'square'),
('anna',  'red',   'triangle'),
('bob',   'blue',  'square'),
('susan', 'blue',  'square'),
('frank', 'red',   'triangle');

内容的提问来源于Stack Exchange，提问作者yolenoyer