查询 删除 重复记录
select t.*, t.rowid from test_select_delete_dump_rd t;
-- 查询表中存在重复记录的所有记录
select t.*, t.rowid from test_select_delete_dump_rd t
where
name in (
select name from test_select_delete_dump_rd
group by name
having count(name) > 1
);
-- 查询表中存在重复记录的多余记录，
-- rowid最小的记录被认为是保留记录
select * from (
select t.*, t.rowid from test_select_delete_dump_rd t
where
name in (
select name from test_select_delete_dump_rd
group by name
having count(name) > 1
)
) t2
where
t2.rowid not in (
select min(t1.rowid) from test_select_delete_dump_rd t1
group by name
having count(name) > 1
);
-- 查询表中存在重复记录的多余记录，
-- rowid最大的记录被认为是保留记录
select * from (
select t.*, t.rowid from test_select_delete_dump_rd t
where
name in (
select name from test_select_delete_dump_rd
group by name
having count(name) > 1
)
) t2
where
t2.rowid not in (
select max(t1.rowid) from test_select_delete_dump_rd t1
group by name
having count(name) > 1
);
/**
delete from test_select_delete_dump_rd
where
name in (
select name from test_select_delete_dump_rd
group by name
having count(name) > 1
)
and
rowid not in (
select min(name) from test_select_delete_dump_rd
group by name
having count(name) > 1
);
*/
