我有一个看起来像这样的表：

mysql> SELECT * FROM Colors;
╔════╦══════════╦════════╦════════╦════════╦════════╦════════╦════════╗
║ ID ║ USERNAME ║  RED   ║ GREEN  ║ YELLOW ║  BLUE  ║ ORANGE ║ PURPLE ║
╠════╬══════════╬════════╬════════╬════════╬════════╬════════╬════════╣
║  1 ║ joe      ║ 1      ║ (null) ║ 1      ║ (null) ║ (null) ║ (null) ║
║  2 ║ joe      ║ 1      ║ (null) ║ (null) ║ (null) ║ 1      ║ (null) ║
║  3 ║ bill     ║ 1      ║ 1      ║ 1      ║ (null) ║ (null) ║ 1      ║
║  4 ║ bill     ║ (null) ║ 1      ║ (null) ║ 1      ║ (null) ║ (null) ║
║  5 ║ bill     ║ (null) ║ 1      ║ (null) ║ (null) ║ (null) ║ (null) ║
║  6 ║ bob      ║ (null) ║ (null) ║ (null) ║ 1      ║ (null) ║ (null) ║
║  7 ║ bob      ║ (null) ║ (null) ║ (null) ║ (null) ║ (null) ║ 1      ║
║  8 ║ bob      ║ 1      ║ (null) ║ (null) ║ (null) ║ (null) ║ (null) ║
╚════╩══════════╩════════╩════════╩════════╩════════╩════════╩════════╝

我想运行一个UPDATE和DELETE来查找和删除重复项并合并记录,以便我们以此结果作为结果.

mysql> SELECT * FROM Colors;
╔════╦══════════╦═════╦════════╦════════╦════════╦════════╦════════╗
║ ID ║ USERNAME ║ RED ║ GREEN  ║ YELLOW ║  BLUE  ║ ORANGE ║ PURPLE ║
╠════╬══════════╬═════╬════════╬════════╬════════╬════════╬════════╣
║  1 ║ joe      ║   1 ║ (null) ║ 1      ║ (null) ║ 1      ║ (null) ║
║  3 ║ bill     ║   1 ║ 1      ║ 1      ║ 1      ║ (null) ║ 1      ║
║  6 ║ bob      ║   1 ║ (null) ║ (null) ║ 1      ║ (null) ║ 1      ║
╚════╩══════════╩═════╩════════╩════════╩════════╩════════╩════════╝

我知道我可以用脚本轻松地做到这一点,但为了更好地学习和理解MySQL,我想学习如何使用纯SQL来做到这一点.

解决方法:

这只是一个预测.它不会更新表,也不会删除某些数据.

SELECT  MIN(ID) ID,
        Username,
        MAX(Red) max_Red,
        MAX(Green) max_Green,
        MAX(Yellow) max_Yellow,
        MAX(Blue) max_Blue,
        MAX(Orange) max_Orange,
        MAX(Purple) max_Purple
FROM    Colors
GROUP   BY Username

> SQLFiddle Demo

UPDATE

如果您确实要删除这些记录,则需要首先运行UPDATE语句,然后才能删除记录

UPDATE  Colors a
        INNER JOIN
        (
            SELECT  MIN(ID) min_ID,
                    Username,
                    MAX(Red) max_Red,
                    MAX(Green) max_Green ,
                    MAX(Yellow) max_Yellow,
                    MAX(Blue) max_Blue,
                    MAX(Orange) max_Orange,
                    MAX(Purple) max_Purple
            FROM    Colors
            GROUP   BY Username
        ) b ON a.ID = b.Min_ID 
SET     a.Red = b.max_Red,
        a.Green = b.max_Green,
        a.Yellow = b.max_Yellow,
        a.Blue = b.max_Blue,
        a.Orange = b.max_Orange,
        a.Purple = b.max_Purple

然后你现在可以删除记录,

DELETE  a
FROM    Colors a
        LEFT JOIN
        (
            SELECT  MIN(ID) min_ID,
                    Username
            FROM    Colors
            GROUP   BY Username
        ) b ON a.ID = b.Min_ID 
WHERE   b.Min_ID  IS NULL

> SQLFiddle Demo