关于保留最高成绩sql(求助)
有一个学生成绩表stu_score(id,score)
1 78
1 88
2 99
2 99
3 45一条删除sql,能够达到如下效果效果如下:
1 88
2 99
3 45要求1、保留用户id最高成绩(最高成绩存在多个,只保留一个)
2、一条删除sql;
有一个学生成绩表stu_score(id,score)
1 78
1 88
2 99
2 99
3 45一条删除sql,能够达到如下效果效果如下:
1 88
2 99
3 45要求1、保留用户id最高成绩(最高成绩存在多个,只保留一个)
2、一条删除sql;
not in (select max(y.score) from stu_score y group by y.id))
or (s.rowid > (select min(e.rowid) from stu_score e
where s.id = e.id))
SQL> select * from stu_score;ID SCORE
---------- ----------
1 78
1 88
2 99
2 99
3 45SQL> delete from stu_score s
2 where
3 (s.score
4 not in (select max(y.score) from stu_score y group by y.id)) or
5 (s.rowid > (select min(e.rowid) from stu_score e
6 where s.id = e.id)
7 );3 rows deleted.SQL> select * from stu_score;ID SCORE
---------- ----------
2 99
3 45SQL>
ID SCORE
---------- ----------
1 78
1 88
2 99
2 99
3 45SQL> delete from stu_score
2 where (id,score,rowid) not in(
3 select id,max(score), max(rowid) row_id
4 from stu_score
5 group by id
6 );2 rows deleted.SQL> select * from stu_score;ID SCORE
---------- ----------
1 88
2 99
3 45
这个方法比较好,利用rowid应该是最快的方式了。。
这位兄弟的代码也有点问题SQL> insert into stu_score select 1,78 from dual
2 union all
3 select 1,88 from dual --1、记录1和记录2调换位置,有问题
4 union all
5 select 2,99 from dual
6 union all
7 select 2,99 from dual
8 union all
9 select 3,45 from dual --这个和新添加的score 比较
10 union all
11 select 3,30 from dual; --2、添加一条记录,30<45 原理和1处的一致,也是有问题的已创建6行。
--先看这个结果
SQL> select id,max(score),max(rowid) row_id
2 from stu_score
3 group by id; ID MAX(SCORE) ROW_ID
---------- ---------- ------------------
1 88 AAAM5mAABAAAO+CAAc
2 99 AAAM5mAABAAAO+CAAe
3 45 AAAM5mAABAAAO+CAAg
--再看最后结果
SQL> delete from stu_score
2 where (id,score,rowid)not in(
3 select id,max(score),max(rowid) row_id
4 from stu_score
5 group by id
6 );已删除4行。SQL> select * from stu_score; ID SCORE
---------- ----------
1 88
2 99
环境:winxp,oracle10g
工具:PL/SQL developer
SQL> desc stu_score;
Name Type Nullable Default Comments
----- ------ -------- ------- --------
ID NUMBER Y
SCORE NUMBER Y 执行前:数据
insert into stu_score values(1,88);
insert into stu_score values(2,99);
insert into stu_score values(3,45);
insert into stu_score values(1,78);
insert into stu_score values(2,99);
insert into stu_score values(2,98);执行后:
1 88
2 99
3 45如果有遗漏,请指出
DELETE FROM stu_score WHERE ROWID IN (
SELECT rd FROM (SELECT ROWID rd,ROW_NUMBER() OVER (PARTITION BY id ORDER BY score DESC,ROWID) rn FROM stu_score) WHERE rn=1);
DELETE FROM stu_score WHERE ROWID NOT IN ( SELECT rd FROM (SELECT ROWID rd,ROW_NUMBER() OVER (PARTITION BY id ORDER BY score DESC,ROWID) rn FROM stu_score) WHERE rn=1);
delete from stu_score where (id,score) not in (select id,max(score) from stu_score
group by id having count(1)>=2)