select 语句只能返回一条记录,这条记录包含两列。
取得班级里成绩最差的学生记录。但是可能会有两个学生的成绩并列最差,这时就返回学号(id)靠前的学生记录
希望大家能够帮我将以下的 select 语句补充完整。谢谢!
SELECT a.Id, a.count
FROM t_ro_stud a
WHERE a.class= '1' order by a.count ;
------解决方案--------------------
select b.id,b.count
from (SELECT a.Id, a.count
FROM t_ro_stud a
WHERE a.class= '1' order by a.count,a.Id ) b
where rownum = 1;
------解决方案--------------------
create table t_ro_stud(id number,count number);
commit;
insert into t_ro_stud values(1,324);
insert into t_ro_stud values(2,324);
insert into t_ro_stud values(3,354);
select * from t_ro_stud;
commit;
select Id,count
from (select a.Id, a.count, row_number() over (order by a.count asc,a.id desc) rank from t_ro_stud a)
where rank = 1;
------解决方案--------------------
SELECT a.Id, a.count,row_number() over(partition by a.Id order by a.count) as r
FROM t_ro_stud a
WHERE a.class= '1' and r=1;