mysql 删除某列重复的行_mysql - 如何从mysql列中存在的所有行中删除重复值[duplicate] - SO中文参考 - www.soinside.com...

有什么方法可以实现所需的输出，我搜索了很多但什么也没得到。您的宝贵意见将受到高度赞赏。

就像我已经说过你应该规范化一样，分隔列表不能强制执行唯一性：无法在不编写应用程序代码或触发器的情况下插入和更新时阻止Red, Blue, Red, Green, Blue。这也意味着获取完整的数据。

如果你没有规范化，那么你将需要具有创造性，使用SQL数字生成器和嵌套的SUBSTRING_INDEX()函数以及CROSS JOIN来分割字符串。并使用GROUP BY和GROUP_CONCAT(DISTINCT ..)来制作独特的价值观

您不希望这样做，此查询显示查询在分隔列表上的难度

询问

SELECT

DISTINCT

t.User_Id

, GROUP_CONCAT(DISTINCT TRIM(SUBSTRING_INDEX(

SUBSTRING_INDEX(

t.Color

, ','

, sql_number_generator.number

)

, ','

, -1

)

)) AS color

FROM (

SELECT

@row := @row + 1 AS number

FROM (

SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9

) row1

CROSS JOIN (

SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9

) row2

CROSS JOIN (

SELECT @row := 0

) init_user_params

) AS sql_number_generator

CROSS JOIN

t

GROUP BY

t.User_Id

结果

| User_Id | color |

| ------- | -------------- |

| 1 | Blue,Green,Red |

| 2 | Blue,Green,Red |

| 3 | Black,White |

| 4 | Red |

但问题是如何用这个结果更新颜色列，我试过但它抛出一个错误[操作数应该包含1列]，我无法理解它是如何完成的

仍然不能相信你愿意继续这种方法。警告之后

UPDATE

t

INNER JOIN (

SELECT

DISTINCT

t.User_Id

, GROUP_CONCAT(DISTINCT TRIM(SUBSTRING_INDEX(

SUBSTRING_INDEX(

t.Color

, ','

, sql_number_generator.number

)

, ','

, -1

)

)) AS color

FROM (

SELECT

@row := @row + 1 AS number

FROM (

SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9

) row1

CROSS JOIN (

SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9

) row2

CROSS JOIN (

SELECT @row := 0

) init_user_params

) AS sql_number_generator

CROSS JOIN

t

GROUP BY

t.User_Id

) AS records_to_updated

SET t.Color = records_to_updated.color

WHERE

t.User_Id = records_to_updated.User_Id