有唯一键的情况:
直接子查询结果删除Mysql不支持的(Oracle 倒是可以)
DELETE
FROM
need_delete_table
WHERE
need_delete_table.lsh IN (
SELECT
t.lsh
FROM
need_delete_table t
WHERE
t.RENYUANBIAOSHI IN ( SELECT z.RENYUANBIAOSHI FROM need_delete_table z GROUP BY z.RENYUANBIAOSHI HAVING count( t.RENYUANBIAOSHI ) > 1 )
AND t.lsh NOT IN ( SELECT max( x.lsh ) FROM need_delete_table x GROUP BY x.RENYUANBIAOSHI HAVING count( x.RENYUANBIAOSHI ) > 1 )
-- AND t.RENYUANBIAOSHI = '100007938'
)
可以尝试以下方式:
delete from need_delete_table where lsh in
(
select n.lsh from (
select t.lsh from need_delete_table t
where t.id in (
select z.id from need_delete_table z
GROUP BY z.id
HAVING count(z.id ) > 1
)
and t.lsh not in (
select max(x.lsh) from need_delete_table x
GROUP BY x.id
HAVING count(x.id ) > 1
)
-- and t.id = '100007938'
) as n
);
无唯一键的情况:
暂时只能想到用脚本和SQL组合删除.
或者考虑使用添加个临时字段作为唯一键,使用上面的方式处理.
代码 待补充。