34,838
社区成员




select 分数,名次=rank() over(order by 分数 desc),id from tb
--> Title : Generating test data [tb]
--> Author : wufeng4552
--> Date : 2009-11-02 10:40:59
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (id int,fenshu int)
insert into [tb]
select 1,22 union all
select 2,23 union all
select 3,1 union all
select 4,1 union all
select 5,21 union all
select 6,2 union all
select 7,2 union all
select 8,22
select
fenshu,
名次=(select count(distinct fenshu) from tb where fenshu>=t.fenshu),
id
from tb t
order by (select count(distinct fenshu) from tb where fenshu>=t.fenshu)
/*
fenshu 名次 id
----------- ----------- -----------
23 1 2
22 2 1
22 2 8
21 3 5
2 4 6
2 4 7
1 5 3
1 5 4
(8 個資料列受到影響)
(8 個資料列受到影響)
*/
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-02 10:41:46
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[fenshu] int)
insert [tb]
select 1,22 union all
select 2,23 union all
select 3,1 union all
select 4,1 union all
select 5,21 union all
select 6,2 union all
select 7,2 union all
select 8,22
--------------开始查询--------------------------
select fenshu as 分数,名次=(select count(fenshu)+1 from tb where t.fenshu>fenshu),id from tb t order by 2
----------------结果----------------------------
/* 分数 名次 id
----------- ----------- -----------
1 1 3
1 1 4
2 3 6
2 3 7
21 5 5
22 6 1
22 6 8
23 8 2
(8 行受影响)
*/
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-02 10:41:46
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[fenshu] int)
insert [tb]
select 1,22 union all
select 2,23 union all
select 3,1 union all
select 4,1 union all
select 5,21 union all
select 6,2 union all
select 7,2 union all
select 8,22
--------------开始查询--------------------------
select fenshu as 分数,名次=(select count(fenshu)+1 from tb where t.fenshu<fenshu),id from tb t order by 2
----------------结果----------------------------
/* 分数 名次 id
----------- ----------- -----------
23 1 2
22 2 1
22 2 8
21 4 5
2 5 6
2 5 7
1 7 3
1 7 4
(8 行受影响)
*/
--> Title : Generating test data [tb]
--> Author : wufeng4552
--> Date : 2009-11-02 10:40:59
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (id int,fenshu int)
insert into [tb]
select 1,22 union all
select 2,23 union all
select 3,1 union all
select 4,1 union all
select 5,21 union all
select 6,2 union all
select 7,2 union all
select 8,22
select
fenshu,
名次=(select count(*) from tb where fenshu>=t.fenshu),
id
from tb t
order by (select count(*) from tb where fenshu>=t.fenshu)
/*
fenshu 名次 id
----------- ----------- -----------
23 1 2
22 3 1
22 3 8
21 4 5
2 6 6
2 6 7
1 8 3
1 8 4
(8 個資料列受到影響)
*/