这只是一个预测。它不会更新表，也不会删除某些数据。
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 演示 http://www.sqlfiddle.com/#!2/cdb3b/1
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 演示 http://www.sqlfiddle.com/#!2/c8635/1
