如何求第二大的记录
表如下:
求java成绩第二高的姓名?
要求:只用用一个SQL语句。
谢谢!
------解决方案--------------------用分析函数
没有测试,不知道有没有错误:
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