MySQL将重复行合并至首行的实现方法示例请求

你之前用concat没得到预期结果，是因为concat仅用于单行内多个字段的拼接，而你需要的是多行同字段的聚合拼接，得用对应数据库的聚合函数来实现。以下是主流数据库的具体操作示例（假设表名为your_table）：

MySQL 操作步骤 #

1. 先查询验证拼接后的结果

SELECT GROUP_CONCAT(no ORDER BY no SEPARATOR ',') AS combined_no
FROM your_table
WHERE name = 'aaa';

2. 更新id=123的行，将拼接结果写入其no字段

UPDATE your_table
SET no = (
    SELECT GROUP_CONCAT(no ORDER BY no SEPARATOR ',')
    FROM your_table
    WHERE name = 'aaa'
)
WHERE id = 123;

3. 删除其他重复的name='aaa'行（保留id=123的行）

DELETE FROM your_table
WHERE name = 'aaa' AND id != 123;

SQL Server 操作步骤 #

1. 查询拼接结果

SELECT STRING_AGG(no, ',') WITHIN GROUP (ORDER BY no) AS combined_no
FROM your_table
WHERE name = 'aaa';

2. 更新目标行

UPDATE your_table
SET no = (
    SELECT STRING_AGG(no, ',') WITHIN GROUP (ORDER BY no)
    FROM your_table
    WHERE name = 'aaa'
)
WHERE id = 123;

3. 删除重复行

DELETE FROM your_table
WHERE name = 'aaa' AND id != 123;

Oracle 操作步骤 #

1. 查询拼接结果

SELECT LISTAGG(no, ',') WITHIN GROUP (ORDER BY no) AS combined_no
FROM your_table
WHERE name = 'aaa';

2. 更新目标行

UPDATE your_table
SET no = (
    SELECT LISTAGG(no, ',') WITHIN GROUP (ORDER BY no)
    FROM your_table
    WHERE name = 'aaa'
)
WHERE id = 123;

3. 删除重复行

DELETE FROM your_table
WHERE name = 'aaa' AND id != 123;

注意事项 #

• 执行删除操作前建议先备份数据，或者用SELECT语句先筛选出要删除的行确认无误后再执行删除。
• 如果no字段是数值类型，部分数据库会自动转换为字符串参与拼接，若出现类型错误可手动用CAST(no AS CHAR)转换。

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