简单的排序问题

[zhousq00]

数据库中表TB
ID fenshu
1 2
2 3
3 5
4 8
5 10
6 16

能不能用一条语句得出10分是在正个表中是排第几的（从高往底排）
即：得出的记过应该是：2

[中国风]

select 
row_number()over(order by fenshu desc) as 排名,*
from t

[rmljoe]

select
id,
fensu,
(select count(1) from tb b where a.fensu >= b.fensu)
from tb a 

[jiangshun]

--> 测试数据：[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([ID] int,[fenshu] int)
insert [TB]
select 1,2 union all
select 2,3 union all
select 3,5 union all
select 4,8 union all
select 5,10 union all
select 6,16

select ID from(
select [fenshu],ID=Row_Number() over(order by ID desc) from [TB])g
where [fenshu]=10

/*
ID
--------------------
2

(1 行受影响)

*/

drop table [TB]

[中国风]

select 
DENSE_RANK()over(order by fenshu desc) as 排名,*
from t

分數相同，並列排名時用DENSE_RANK

[rmljoe]

[Quote=引用 2 楼 rmljoe 的回复:]
SQL code
select
id,
fensu,
(select count(1) from tb b where a.fensu >= b.fensu)
from tb a
[/Quote]

反了，应该是 <=

[zhousq00]

说明一下，我用的是SQL Server 2000数据库

[中国风]

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#') is null
	drop table #
Go
Create table #([ID] int,[fenshu] int)
Insert #
select 1,2 union all
select 2,3 union all
select 3,5 union all
select 4,8 union all
select 5,10 union all
select 6,16 UNION ALL
select 7,16 --加多一個記錄如下

Go
--SQL2005以上版本
select 
DENSE_RANK()over(order by fenshu desc) as 排名,*
from #
ORDER BY 1
--SQL2000

SELECT 
(SELECT COUNT(DISTINCT [fenshu]) FROM # WHERE [fenshu]>t.[fenshu])+1as 排名
,*
from # AS t

ORDER BY 1

/*

(7 個資料列受到影響)
排名                   ID          fenshu
-------------------- ----------- -----------
1                    6           16
1                    7           16
2                    5           10
3                    4           8
4                    3           5
5                    2           3
6                    1           2

(7 個資料列受到影響)

排名          ID          fenshu
----------- ----------- -----------
1           6           16
1           7           16
2           5           10
3           4           8
4           3           5
5           2           3
6           1           2

(7 個資料列受到影響)


*/

[中国风]

COUNT(DISTINCT [fenshu])--把分數相同的排名相同

[zhousq00]

谢谢，结帖，都是高手！