select id, fensu, (select count(1) from tb b where a.fensu >= b.fensu) from tb a
--> 测试数据:[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,16select 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
说明一下,我用的是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 --SQL2000SELECT (SELECT COUNT(DISTINCT [fenshu]) FROM # WHERE [fenshu]>t.[fenshu])+1as 排名 ,* from # AS tORDER 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 個資料列受到影響) */
id,
fensu,
(select count(1) from tb b where a.fensu >= b.fensu)
from tb a
--> 测试数据:[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,16select ID from(
select [fenshu],ID=Row_Number() over(order by ID desc) from [TB])g
where [fenshu]=10/*
ID
--------------------
2(1 行受影响)*/drop table [TB]
DENSE_RANK()over(order by fenshu desc) as 排名,*
from t分數相同,並列排名時用DENSE_RANK
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
--SQL2000SELECT
(SELECT COUNT(DISTINCT [fenshu]) FROM # WHERE [fenshu]>t.[fenshu])+1as 排名
,*
from # AS tORDER 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 個資料列受到影響)
*/