季布,
2021-10-21 14:43
采纳率: 100%
浏览 44

sql 语句根据多个相同字段条件删除重复记录

img

框起来的部分时重复的记录,如何使用sql语句删除任意一条重复的记录

  • 好问题 提建议
  • 收藏

4条回答 默认 最新

  • PHP技术栈 2021-10-21 20:24
    已采纳

    设表名为:test_user
    DELETE FROM test_user WHERE(type_user,user_u_id,gtrevuser_id)
    IN(SELECT type_user,user_u_id,gtrevuser_id FROM(SELECT type_user,user_u_id,gtrevuser_id FROM test_user GROUP BY type_user,user_u_id,gtrevuser_id HAVING count() > 1) AS A)
    AND id NOT IN(SELECT id FROM(SELECT min(id) AS id FROM test_user GROUP BY type_user,user_u_id,gtrevuser_id HAVING count(
    )>1) AS B)

    DELETE FROM test_user WHERE(type_user,user_u_id,gtrevuser_id)
    IN(SELECT type_user,user_u_id,gtrevuser_id FROM(SELECT type_user,user_u_id,gtrevuser_id FROM test_user GROUP BY type_user,user_u_id,gtrevuser_id HAVING count() > 1) AS A)
    AND id NOT IN(SELECT id FROM(SELECT min(id) AS id FROM test_user GROUP BY type_user,user_u_id,gtrevuser_id HAVING count()>1) AS B)
    
    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • 离人散 2021-10-21 14:58

    DELETE A
    FROM Table A ,
    (SELECT Para1,Para2 FROM Table
    GROUP BY Para1,Para2
    HAVING COUNT() > 1 ) B
    WHERE A.Para1=B.Para1 AND a.Para2= b.Para2
    AND A.ID NOT IN (SELECT MIN(ID) AS ID
    FROM Table
    GROUP BY Para1,Para2
    HAVING COUNT(
    ) > 1)

    评论
    解决 无用
    打赏 举报
  • zerobyte_ 2021-10-21 14:59

    delete from 表明 where type_user in (select type_user from 表名 group by type_user having count(type_user) > 1)

    评论
    解决 无用
    打赏 举报
  • 红尘摆渡803 2021-10-25 14:43

    Oracle数据库吗? 推荐使用rowid哦.
    DELETE FROM test_user WHERE rowid not in (select max(rowid) from test_user group by type_user,user_u_id,gtrevuser_id having count(1)>1 )

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题