【SQL练习】用一条sql删除除了id不同，其它信息完全相同的冗余信息

用一条sql删除除了id不同，其它信息完全相同的冗余信息

create table tab1 (
       id number(1),
       stuId varchar2(7),
       name varchar2(20),
       courseID number(2),
       courseName varchar2(20),
       score number(3)
)

insert into tab1(id, stuId, name, courseID, courseName, score) 
values (1, '2005001', '张三', 1, '数学', 69);
insert into tab1(id, stuId, name, courseID, courseName, score) 
values (2, '2005002', '李四', 1, '数学', 89);
insert into tab1(id, stuId, name, courseID, courseName, score) 
values (3, '2005001', '张三', 1, '数学', 69);
commit;

用一条sql删除除了id不同，其它信息完全相同的冗余信息

delete from tab1 where tab1.id in (
with temp as
 (select t.*,
         rank() over(partition by t.stuid, t.name, t.courseid, t.coursename, t.score order by t.id) rk
    from tab1 t)
select temp.id from temp where temp.rk > 1);
commit;