用分析函数 没有测试,不知道有没有错误:select name from (select t.*,row_number() over(order by kemu desc) rn from t where kemu='JAVA') where rn=2
with t as (select 1 as ID, 'Java' as course, 'Jim' as name, '80' as score from dual union all select 2 as ID, 'Java' as course, 'Lilei' as name, '90' as score from dual union all select 3 as ID, 'Java' as course, 'Hanmeimei' as name, '60' as score from dual union all select 4 as ID, 'C' as course, 'Tom' as name, '100' as score from dual
) select name from (select name, row_number() over(partition by course order by score desc) as rn from t where course = 'Java') where rn = 2;
在介绍一种比较少见的写法,使用connect by with t as (select 1 as ID, 'Java' as course, 'Jim' as name, '80' as score from dual union all select 2 as ID, 'Java' as course, 'Lilei' as name, '90' as score from dual union all select 3 as ID, 'Java' as course, 'Hanmeimei' as name, '60' as score from dual union all select 4 as ID, 'C' as course, 'Tom' as name, '100' as score from dual
) select max(score), level from t where course = 'Java' connect by prior score > score group by level having level = 2;
select * from (select name,score from test where kemu = 'java' order by score desc) where rownum < 3 minus select * from (select name,score from test where kemu = 'java' order by score desc) where rownum < 2
SCOTT@xp > select * from score; ID KEMU SCORE NAME ---------- ------ ---------- ---------- 1 JAVA 80 赵 2 C 100 钱 3 JAVA 60 孙 4 C 100 李 SCOTT@xp > select rownum,a.* from (select name,score,kemu from score where kemu='JAVA' order by score )a where rownum<=1; ROWNUM NAME SCORE KEMU ---------- ---------- ---------- ------ 1 孙 60 JAVA 这样就行了,弄个rownum就可以取出来
select name from score where score=(select max(score) from score where score<(select max(score) from score)); 比较啰嗦,仅供参考
不好意思,我的那个sql没有判断科目的条件
select name from (select rank() over (order by score desc) rank,t.* from table t) where rank = 2 and kemu = 'java'; 这样应该没问题吧。
没有测试,不知道有没有错误:select name from (select t.*,row_number() over(order by kemu desc) rn from t where kemu='JAVA') where rn=2
with t as
(select 1 as ID, 'Java' as course, 'Jim' as name, '80' as score
from dual
union all
select 2 as ID, 'Java' as course, 'Lilei' as name, '90' as score
from dual
union all
select 3 as ID, 'Java' as course, 'Hanmeimei' as name, '60' as score
from dual
union all
select 4 as ID, 'C' as course, 'Tom' as name, '100' as score from dual
)
select name
from (select name,
row_number() over(partition by course order by score desc) as rn
from t
where course = 'Java')
where rn = 2;
如果第二高的分数有多个人,你只要一个就用row_number如果全部要,就用dense_rank
with t as
(select 1 as ID, 'Java' as course, 'Jim' as name, '80' as score
from dual
union all
select 2 as ID, 'Java' as course, 'Lilei' as name, '90' as score
from dual
union all
select 3 as ID, 'Java' as course, 'Hanmeimei' as name, '60' as score
from dual
union all
select 4 as ID, 'C' as course, 'Tom' as name, '100' as score from dual
)
select max(score), level
from t
where course = 'Java'
connect by prior score > score
group by level
having level = 2;
minus
select * from (select name,score from test where kemu = 'java' order by score desc) where rownum < 2
---------- ------ ---------- ----------
1 JAVA 80 赵
2 C 100 钱
3 JAVA 60 孙
4 C 100 李
SCOTT@xp > select rownum,a.* from (select name,score,kemu from score where kemu='JAVA' order by score )a where rownum<=1; ROWNUM NAME SCORE KEMU
---------- ---------- ---------- ------
1 孙 60 JAVA
这样就行了,弄个rownum就可以取出来
比较啰嗦,仅供参考
这样应该没问题吧。