select 排名 = (select count(1) from tb where [浮点数]>=t.[浮点数] and id <> t.id) from tb t
select 排名 = (select count(1) from tb where [浮点数]>=t.[浮点数] and id <> t.id) from tb t where id = 输入id
DECLARE @tb TABLE( ID INT, Val float )INSERT INTO @tb (ID,Val) SELECT 2,1.56 UNION ALL SELECT 5,2.38 UNION ALL SELECT 6,3.89 UNION ALL SELECT 4,4.79 UNION ALL SELECT 1,5.62 UNION ALL SELECT 3,6.68SELECT * FROM @tb;WITH OrderByVal AS ( SELECT *,ROW_NUMBER() OVER(ORDER BY Val desc) AS rownum FROM @tb )SELECT rownum FROM OrderByVal WHERE ID=4
来个有语法显示的..DECLARE @tb TABLE( ID INT, Val float )INSERT INTO @tb (ID,Val) SELECT 2,1.56 UNION ALL SELECT 5,2.38 UNION ALL SELECT 6,3.89 UNION ALL SELECT 4,4.79 UNION ALL SELECT 1,5.62 UNION ALL SELECT 3,6.68SELECT * FROM @tb;WITH OrderByVal AS ( SELECT *,ROW_NUMBER() OVER(ORDER BY Val desc) AS rownum FROM @tb )SELECT rownum FROM OrderByVal WHERE ID=4 --------执行结果------ (6 行受影响) ID Val ----------- ---------------------- 2 1.56 5 2.38 6 3.89 4 4.79 1 5.62 3 6.68(6 行受影响)rownum -------------------- 3(1 行受影响)
from tb
order by 浮点数列 desc
select 排名 = (select count(1) from tb where [浮点数]>=t.[浮点数] and id <> t.id)
from tb t
select 排名 = (select count(1) from tb where [浮点数]>=t.[浮点数] and id <> t.id)
from tb t
where id = 输入id
ID INT,
Val float
)INSERT INTO @tb (ID,Val)
SELECT 2,1.56 UNION ALL
SELECT 5,2.38 UNION ALL
SELECT 6,3.89 UNION ALL
SELECT 4,4.79 UNION ALL
SELECT 1,5.62 UNION ALL
SELECT 3,6.68SELECT * FROM @tb;WITH OrderByVal
AS
(
SELECT *,ROW_NUMBER() OVER(ORDER BY Val desc) AS rownum FROM @tb
)SELECT rownum FROM OrderByVal WHERE ID=4
ID INT,
Val float
)INSERT INTO @tb (ID,Val)
SELECT 2,1.56 UNION ALL
SELECT 5,2.38 UNION ALL
SELECT 6,3.89 UNION ALL
SELECT 4,4.79 UNION ALL
SELECT 1,5.62 UNION ALL
SELECT 3,6.68SELECT * FROM @tb;WITH OrderByVal
AS
(
SELECT *,ROW_NUMBER() OVER(ORDER BY Val desc) AS rownum FROM @tb
)SELECT rownum FROM OrderByVal WHERE ID=4
--------执行结果------
(6 行受影响)
ID Val
----------- ----------------------
2 1.56
5 2.38
6 3.89
4 4.79
1 5.62
3 6.68(6 行受影响)rownum
--------------------
3(1 行受影响)