如何修改MySQL重复行查询语句以新增ID列表列

你原来的子查询方案存在两个核心问题：

• 标量子查询要求返回单一结果，但你的子查询会返回多行匹配的ID，数据库会持续尝试处理这种矛盾逻辑，导致长时间加载
• 大表下这种逐行关联的子查询无索引优化时，性能会极低

针对不同数据库，推荐使用原生的字符串聚合函数实现需求，同时配合索引优化提升大表查询效率：

MySQL/MariaDB 方案 #

使用GROUP_CONCAT函数直接拼接分组内的ID：

SELECT 
    firstcolumn,
    COUNT(*) AS c,
    GROUP_CONCAT(secondid ORDER BY secondid SEPARATOR ',') AS ids
FROM invoice
WHERE firstcolumn != ''
GROUP BY firstcolumn
HAVING c > 1
ORDER BY c DESC;

• 可选参数：ORDER BY secondid让ID按顺序排列，SEPARATOR ','指定分隔符（默认即为逗号）
• 注意：若ID数量过多，需调整group_concat_max_len参数避免结果截断
• 性能优化：给invoice表创建联合索引idx_invoice_first_second (firstcolumn, secondid)，大幅提升分组和聚合速度

PostgreSQL 方案 #

使用STRING_AGG函数：

SELECT 
    firstcolumn,
    COUNT(*) AS c,
    STRING_AGG(CAST(secondid AS TEXT), ',' ORDER BY secondid) AS ids
FROM invoice
WHERE firstcolumn != ''
GROUP BY firstcolumn
HAVING COUNT(*) > 1
ORDER BY c DESC;

• 若secondid是数值类型，需要通过CAST转为文本类型才能拼接
• 性能优化：创建联合索引idx_invoice_first_second (firstcolumn, secondid)

SQL Server 方案 #

2017及以上版本（支持STRING_AGG） #

SELECT 
    firstcolumn,
    COUNT(*) AS c,
    STRING_AGG(secondid, ',' ) WITHIN GROUP (ORDER BY secondid) AS ids
FROM invoice
WHERE firstcolumn != ''
GROUP BY firstcolumn
HAVING COUNT(*) > 1
ORDER BY c DESC;

2016及以前版本（用XML拼接方式） #

SELECT 
    si.firstcolumn,
    COUNT(*) AS c,
    STUFF(
        (SELECT ',' + CAST(so.secondid AS VARCHAR(20)) 
         FROM invoice so 
         WHERE so.firstcolumn = si.firstcolumn
         ORDER BY so.secondid
         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
        1, 1, ''
    ) AS ids
FROM invoice si
WHERE si.firstcolumn != ''
GROUP BY si.firstcolumn
HAVING COUNT(*) > 1
ORDER BY c DESC;

• 此方法通过XML拼接字符串后，用STUFF移除开头多余的逗号
• 性能优化：创建联合索引idx_invoice_first_second (firstcolumn, secondid)

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