MySQL去重

找出重复记录

SELECT
*
FROM
unitednations_yearly_world a
WHERE
(a.cmdCode,a.rtCode,a.NetWeight,a.TradeValue,a.ptCode,a.rgCode) IN (
SELECT
cmdCode,rtCode,NetWeight,TradeValue,ptCode,rgCode
FROM
unitednations_yearly_world
GROUP BY
cmdCode,rtCode,NetWeight,TradeValue,ptCode,rgCode
HAVING
count(*) > 1
)

 

删除重复记录

DELETE
FROM
unitednations_yearly_world a
WHERE
(a.cmdCode,a.rtCode,a.NetWeight,a.TradeValue,a.ptCode,a.rgCode) IN (
SELECT
cmdCode,rtCode,NetWeight,TradeValue,ptCode,rgCode
FROM
unitednations_yearly_world
GROUP BY
cmdCode,rtCode,NetWeight,TradeValue,ptCode,rgCode
HAVING
count(*) > 1
)
AND id NOT IN (
SELECT
min(id)
FROM
unitednations_yearly_world GROUP BY
cmdCode,rtCode,NetWeight,TradeValue,ptCode,rgCode HAVING
count(*) > 1
)