表A
数据
name grade
tom 60
john 89
green 70
tim 100
nancy 100
查询语句输出结果
name grade race
nancy 100 1
tim 100 1
john 89 2
green 70 3
tom 60 4
写出一条sql实现上面的结果
[quote]ls的做法产生的名次为1,1,3....[/quote]
[code="java"]select name,grade,dense_rank() over(order by grade desc) from A [/code]
[code="java"]select name,grade,rank() over(order by grade desc) from A[/code]
[code="sql"]
select A.name, A.grade, B.race left join
(select grade,rank() over(order by grade desc) race
from (select distinct grade from A) t ) B
on A.grade = B.grade
[/code]
ls的做法产生的名次为1,1,3....
纠正一下 :D
[code="sql"]
select A.name, A.grade, B.race from A left join
(select grade,rank() over(order by grade desc) race
from (select distinct grade from A) t ) B
on A.grade = B.grade
[/code]
select name,grade,dense_rank() over(partition by name order by grade desc) from A
select name,grade,rownum as race from a order by grade desc,name asc
select a.name,a.grade,(select count(*)+1 from A b where a.grade < b.grade) race from A a order a.race ;