求“查询某条记录关于某字段的排名”sql

[SnowDream]

表A
ID NAME MONEY
1 张松钠 50
2 李四 58
3 王五 46

怎么查询出李四信息的同时，获取他的money的排名

哪位能指点下。。。。

[SQL77]

SELECT *,(SELECT COUNT(1) FROM A WHERE MONEY>=T.MONEY)AS NUM FROM A T

[SQL77]

----------------------------------------------------------------
-- Author  :SQL77(只为思齐老)
-- Date    :2010-03-03 09:11:24
-- Version:
--      Microsoft SQL Server  2000 - 8.00.194 (Intel X86) 
--	Aug  6 2000 00:57:48 
--	Copyright (c) 1988-2000 Microsoft Corporation
--	Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go 
create table #TB([ID] int,[NAME] varchar(6),[MONEY] int)
insert #TB
select 1,'张松钠',50 union all
select 2,'李四',58 union all
select 3,'王五',46
--------------开始查询--------------------------

SELECT 
	*,
	(SELECT COUNT(1) FROM #TB WHERE MONEY>=T.MONEY)AS NUM 
FROM
 	#TB T 
ORDER BY 4
----------------结果----------------------------
/* 

（所影响的行数为 3 行）

ID          NAME   MONEY       NUM         
----------- ------ ----------- ----------- 
2           李四     58          1
1           张松钠    50          2
3           王五     46          3

（所影响的行数为 3 行）


*/

[快乐_石头]

if not object_id('tb') is null
	drop table tb
Go
Create table tb([ID] int,[NAME] nvarchar(3),[MONEY] int)
Insert tb
select 1,N'张松钠',50 union all
select 2,N'李四',58 union all
select 3,N'王五',46
Go
--2005
Select * ,
       排名=row_number()over(order by [MONEY]desc)
from tb
---2000
Select * ,
       排名=(select count(*) from tb where [MONEY]>=t.[MONEY])
from tb t
/*
ID          NAME MONEY       排名
----------- ---- ----------- --------------------
2           李四   58          1
1           张松钠  50          2
3           王五   46          3
*/

[黄_瓜]

引用 3 楼 happy_stone 的回复:

SQL codeifnotobject_id('tb')isnulldroptable tbGoCreatetable tb([ID]int,[NAME]nvarchar(3),[MONEY]int)Insert tbselect1,N'张松钠',50unionallselect2,N'李四',58unionallselect3,N'王五',46Go--2005Select* ,
排?-

up

[ws_hgo]

if not object_id('tb') is null
    drop table tb
Go
Create table tb([ID] int,[NAME] nvarchar(3),[MONEY] int)
Insert tb
select 1,N'张松钠',50 union all
select 2,N'李四',58 union all
select 3,N'王五',46
Go

select *,
       (select count(*)+1 from tb where [MONEY]<t.[MONEY]) '排名'
 from tb t where [Name]='李四'

ID          NAME MONEY       排名
----------- ---- ----------- -----------
2           李四   58          3

(1 行受影响)

[ws_hgo]

select *,
       (select count(*)+1 from tb where [MONEY]>t.[MONEY]) '排名'
 from tb t where [Name]='李四'


ID          NAME MONEY       排名
----------- ---- ----------- -----------
2           李四   58          1

(1 行受影响)

[SnowDream]

谢谢大家。。。感动涕零，，，

[ShenLiang2025]

Hi,

Appending

0)表和数据
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go 
create table #TB([ID] int,[NAME] varchar(6),[MONEY] int)
insert #TB
select 1,'张松钠',50 union all
select 2,'李四',58 union all
select 3,'王五',46 union all
select 4,'张三',58 union all
select 5,'王六',68 

--SELECT  * FROM #TB

1)定义CTE使之按照MONEY逆排序
WITH CTE AS
(SELECT TOP(SELECT COUNT(ID)FROM #TB) 
* FROM #TB
ORDER BY MONEY DESC)

SELECT * FROM CTE

2)附加排序的值RANK

SELECT *,RANK = ROW_NUMBER()OVER(ORDER BY MONEY DESC,ID)
FROM CTE

3)结果展示
ID          NAME   MONEY       RANK
----------- ------ ----------- --------------------
5           王六     68          1
2           李四     58          2
4           张三     58          3
1           张松钠   50          4
3           王五     46          5

(5 row(s) affected)