sql删除冗余数据

工作或面试中经常能遇见一种场景题：删除冗余的数据，以下是举例介绍相应的解决办法。

• 举例：
  • 表结构：
    
  • 解法1：子查询
    获取相同数据中id更小的数据项，再将id不属于其中的数据删除。

    -- 注意：mysql中不允许在一个语句中同时读取和更新同一张表，会出现问题；为了避开这一限制，可以嵌套一个子查询，操作临时表
    DELETE 
    FROMstudent 
    WHEREid NOT IN (SELECT* FROM( SELECT MIN( id ) AS id FROM student GROUP BY stu_no, NAME, course_no, course_name, score ) AS subquery );
    

  • 解法2：join自连接

    -- 从s1中删除记录，删除相同数据项中id更大的（删除delete后面的s1不能执行，因为该语句中涉及s1和s2，会不知道删除s1还是s2）
    DELETE s1 
    FROMstudent AS s1LEFT JOIN student AS s2 ON s1.stu_no = s2.stu_no AND s1.`name` = s2.`name` AND s1.course_no = s2.course_no AND s1.course_name = s2.course_name AND s1.score = s2.score 
    WHEREs1.id > s2.id
    

  • 解法3：分区排序删除

    -- 先创建临时表，再根据临时表中的结果删除原表（使用row_number()先分区再排序，序号大于1的就是冗余数据）
    WITH CTE AS(SELECT * , ROW_NUMBER() over (PARTITION by stu_no, name, course_no, course_name, score ORDER BY id) as row_numFROM student
    )DELETE from student WHERE id in (select id from CTE where row_num > 1)
    

以上为个人学习分享，如有问题，欢迎指出：）