关于保留最高成绩sql(高级)

Question

关于保留最高成绩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;

Answer 1

delete from stu_score s where (s.score 
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)) 

Answer 2

呵呵，发现和ms sql不太一样ms sql 里可以的，但是oracle 却不行

Answer 3

这个sql你测试过吗？？
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> 

Answer 4

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

Answer 5

这个方法比较好，利用rowid应该是最快的方式了。。

Answer 6

这位兄弟的代码也有点问题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

Answer 7

当然测试过了，
环境: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如果有遗漏，请指出

Answer 8

试试这个：
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);

Answer 9

NOT IN
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);

Answer 10

delete from stu_score where (id,score) not in (select id,max(score) from stu_score
group by id having count(1)>=2)