一道sql的面试题,感觉没有一点思路,各位大哥看看呗

表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 ;