查询表中多余的重复记录（个人学习记录）

最新推荐文章于 2023-06-26 23:22:13 发布

一无用处的菜鸟

最新推荐文章于 2023-06-26 23:22:13 发布

阅读量77

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/weixin_44494593/article/details/127637791

版权

查询表中多余的重复记录（多个字段），不包含id最小的记录

select from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count() > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1

删除重复的权限记录

原因：在查询表的时候进行删除会报异常

DELETE
FROM
    mj_reguser
WHERE
    id IN (
    select m.id from
     
    (SELECT
        id
    FROM
        mj_reguser A,
        ( SELECT sys_no,door_id FROM mj_reguser GROUP BY sys_no,door_id HAVING COUNT(*) > 1 ) B
    WHERE
        A.sys_no  = B.sys_no 
        AND A.door_id  = B.door_id
    AND A.id NOT IN ( SELECT MIN( id ) AS ID FROM mj_reguser GROUP BY sys_no,door_id HAVING COUNT(*) > 1 )) m
    )