关于保留最高成绩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;
解决方案 »
- SQL优化的东东,大家帮个忙
- 一个库内,表a中3亿条数据分别入表b,表c,表d!如何做,效率最高
- 崩溃中。。 怎么才可以查出来、?。。
- 请大家帮忙看看ORACLE ERP客户档案号是怎样设置的,先谢谢各位了
- select * from table where statement 的存储过程
- 急待解决oracle新建用户并导入备份问题~~求助帮忙
- 在线等急 怎么将ORA文件导入到ORACLE
- 取表前5条记录,oracle的sql语句怎么写?
- 请问怎样在Window2000中移动ORACLE的数据库文件?
- 谁能将在ORACLE存贮过程中,使用游标的情况给讲解一下,最好提供几个例子。
- Oracle插入特殊字符??
- JOB 不能重复调用,运行一次就没有了,有无例子
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)