mysql重复记录处理
这里记录一下用到的语句和语句模板:
-- 查询出重复的数据
SELECT
COUNT(*) as repeats,
address,
signer_name,
signer_mobile
FROM user_operation_useraddress
GROUP BY
address,
signer_name,
signer_mobile
HAVING
repeats > 1;
-- 查询出重复的数据中最小的id
SELECT
MIN(id) as min_id,
COUNT(*) as repeats,
address,
signer_name,
signer_mobile
FROM user_operation_useraddress
GROUP BY
address,
signer_name,
signer_mobile
HAVING
repeats > 1;
-- 查出重复的数据中非最小的id(需要删除的)
SELECT
id
FROM user_operation_useraddress
WHERE
(address, signer_name, signer_mobile) IN (
SELECT
address,
signer_name,
signer_mobile
FROM user_operation_useraddress
GROUP BY
address,
signer_name,
signer_mobile
HAVING
COUNT(*) > 1
)
AND id NOT IN (
SELECT
MIN(id)
FROM user_operation_useraddress
GROUP BY
address,
signer_name,
signer_mobile
HAVING
COUNT(*) > 1
);
-- 建立一张临时表t
SELECT
id
FROM (
SELECT
id
FROM user_operation_useraddress
WHERE
(address, signer_name, signer_mobile) IN (
SELECT
address,
signer_name,
signer_mobile
FROM user_operation_useraddress
GROUP BY
address,
signer_name,
signer_mobile
HAVING
COUNT(*) > 1
)
AND id IN (
SELECT
MIN(id)
FROM user_operation_useraddress
GROUP BY
address,
signer_name,
signer_mobile
HAVING
COUNT(*) > 1
)
) as t;
-- 删除重复的数据(只保留一条,保留最小id的)
DELETE FROM user_operation_useraddress
WHERE
id IN (
SELECT
id
FROM (
SELECT
id
FROM user_operation_useraddress
WHERE
(address, signer_name, signer_mobile) IN (
SELECT
address,
signer_name,
signer_mobile
FROM user_operation_useraddress
GROUP BY
address,
signer_name,
signer_mobile
HAVING
COUNT(*) > 1
)
AND id IN (
SELECT
MIN(id)
FROM user_operation_useraddress
GROUP BY
address,
signer_name,
signer_mobile
HAVING
COUNT(*) > 1
)
) as t
);
posted on 2023-06-01 11:02 zyp_java_net 阅读(7) 评论(0) 编辑 收藏 举报