如何求第二大的记录
表如下：


求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
[解决办法]
在介绍一种比较少见的写法，使用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';
这样应该没问题吧。
[解决办法]
推荐用分析函数