select rownum from (select name,score from man_info order by score desc) t where name='zs'
如果一定要一句话搞定,可以用用这个 select name,score, no from (select name,score,rownum as no from (select name,score from man_info order by score desc) t) where name='ls';三重select套用,呵呵,这样做数据大的时候,可能会很慢的!
按照楼上第一条sql说得 SQL> select name,score,rownum from 2 (select name,score from man_info order by score desc) t 3 ;NAME SCORE ROWNUM -------------------- ---------- ---------- wu 100 1 ls 95 2 zs 85 3这不是我想要得结果;按照楼上第二条sql说得 SQL> select rownum from 2 (select name,score from man_info order by score desc) t 3 where name='zs' 4 ; ROWNUM ---------- 1这也不是我想要的结果,因为实际上zs排名为3;其实我想要的结果是 ROWNUM ---------- 3用一条sql能不能实现这个功能,或者要通过创建一个临时表实现吗? 请高手继续指点!
按照楼上第一条sql说得 SQL> select name,score,rownum from 2 (select name,score from man_info order by score desc) t 3 ;NAME SCORE ROWNUM -------------------- ---------- ---------- wu 100 1 ls 95 2 zs 85 3这不是我想要得结果;按照楼上第二条sql说得 SQL> select rownum from 2 (select name,score from man_info order by score desc) t 3 where name='zs' 4 ; ROWNUM ---------- 1这也不是我想要的结果,因为实际上zs排名为3;其实我想要的结果是 ROWNUM ---------- 3用一条sql能不能实现这个功能,或者要通过创建一个临时表实现吗? 请高手继续指点!
假如我需要查询zs(具体哪一个人不固定)的排名,该怎么写sql!
(select name,score from man_info order by score desc) t
where name='zs'
select name,score, no from (select name,score,rownum as no from (select name,score from man_info order by score desc) t) where name='ls';三重select套用,呵呵,这样做数据大的时候,可能会很慢的!
select * from
(select name,row_number() over(order by score desc) num from man_info ) t
where name='sz'
SQL> select name,score,rownum from
2 (select name,score from man_info order by score desc) t
3 ;NAME SCORE ROWNUM
-------------------- ---------- ----------
wu 100 1
ls 95 2
zs 85 3这不是我想要得结果;按照楼上第二条sql说得
SQL> select rownum from
2 (select name,score from man_info order by score desc) t
3 where name='zs'
4 ; ROWNUM
----------
1这也不是我想要的结果,因为实际上zs排名为3;其实我想要的结果是
ROWNUM
----------
3用一条sql能不能实现这个功能,或者要通过创建一个临时表实现吗?
请高手继续指点!
SQL> select name,score,rownum from
2 (select name,score from man_info order by score desc) t
3 ;NAME SCORE ROWNUM
-------------------- ---------- ----------
wu 100 1
ls 95 2
zs 85 3这不是我想要得结果;按照楼上第二条sql说得
SQL> select rownum from
2 (select name,score from man_info order by score desc) t
3 where name='zs'
4 ; ROWNUM
----------
1这也不是我想要的结果,因为实际上zs排名为3;其实我想要的结果是
ROWNUM
----------
3用一条sql能不能实现这个功能,或者要通过创建一个临时表实现吗?
请高手继续指点!