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