按分数排名次

[anly_hz]

表test

name score

张三 55
李四 65
王五 65
赵六 82
王麻子 70

我需要得到如下结果，该怎么做，先谢谢各位了！
name score ranking

赵六 82 5
王麻子 70 4
王五 65 3
李四 65 3
张三 55 1

[anly_hz]

非常感谢各位，经测试，3楼正解。
select name, score, rank() over (order by score desc ) ranking from tmp;
3楼：
over (order by score desc ) 可以得出正确结果
5，6楼：
over (order by score) 得出的结果有误

3楼的nGX20080110这位高人能解释下吗

[心中的彩虹]

[Quote=引用 7 楼 anly_hz 的回复:]
非常感谢各位，经测试，3楼正解。
select name, score, rank() over (order by score desc ) ranking from tmp;
3楼：
over (order by score desc ) 可以得出正确结果
5，6楼：
over (order by score) 得出的结果有误

3楼的nGX20080110这位高人能解释下吗
……
[/Quote]
都没有错误 只是按你的结果意思来的

[minitoy]

分析函数RANK()

[华夏小卒]

drop table test;
create table test(name varchar(10),score int);

insert into test select '张三',  55 from dual;
insert into test select '李四',  65 from dual;
insert into test select '王五',  65 from dual;
insert into test select '赵六',  82 from dual;
insert into test select '王麻子',70 from dual;


select name,score,(select count(score) from test where score<=t.score) as ranking
from test t
 order by ranking desc

NAME  SCORE  RANKING  
赵六  82  5  
王麻子  70  4  
李四  65  3  
王五  65  3  
张三  55  1 

[ngx20080110]

with tmp as 
(
select '张三' name, 55 score from dual union all
select '李四' name,  65 score from dual union all
select '王五' name,  65 score from dual union all
select '赵六' name,  82 score from dual union all
select '王麻子' name,  70 score from dual
)
select name, score, rank() over (order by score desc ) ranking
from tmp;

NAME               SCORE    RANKING
------------- ---------- ----------
赵六                    82          1
王麻子                   70          2
李四                    65          3
王五                    65          3
张三                    55          5

[duqiangcise]

select * ,row_number()over(partition by name order by score desc) ranking
from test;

[Phoenix_99]

select name,score,rank() over(order by score) ranking from test order by score desc

[心中的彩虹]

[Quote=引用楼主 anly_hz 的回复:]
表test

name score

张三 55
李四 65
王五 65
赵六 82
王麻子 70

我需要得到如下结果，该怎么做，先谢谢各位了！
name score ranking

赵六 82 5
王麻子 70 4
王五 65 3
李四 65 3
张三 55 1
[/Quote]

  1  with tb as
  2  (
  3  select '张三' name, 55 score from dual union all
  4  select '李四' name,  65 score from dual union all
  5  select '王五' name,  65 score from dual union all
  6  select '赵六' name,  82 score from dual union all
  7  select '王麻子' name,  70 score from dual
  8  )
  9  select * from (select name,score,rank() over(order by score) ranking from tb)
 10* order by ranking desc
SQL> /

NAME        SCORE    RANKING
------ ---------- ----------
赵六           82          5
王麻子         70          4
李四           65          2
王五           65          2
张三           55          1