查询 删除 重复记录

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
    );
*/