计算满足条件时列中的位置前面有多少行

Question

如何计算 2 楼 Jane 领先多少人(不包括 1 楼的人(？

+------+---------+----------+
|Index |  Name   |   Floor  |
+------+---------+----------+
|   1  | Sally   |     1    |
|   2  | Sue     |     1    |
|   3  | Fred    |     1    |
|   4  | Wally   |     2    |
|   5  | Tommy   |     2    |
|   6  | Jane    |     2    |
|   7  | Bart    |     2    |
|   8  | Sam     |     3    |
+------+---------+----------+

预期结果是 2，因为 2 楼有 2 个人(沃利和汤米(在简前面。

我尝试使用 CHARINDEX 从我生成的临时表中查找行号，但这似乎不起作用：

SELECT CHARINDEX('Jane', Name) as position
INTO #test
FROM tblExample
WHERE Floor = 2
select ROW_NUMBER() over (order by position) from #test
WHERE position = 1

Answer 1

我认为一个简单的row_number()就可以了

Select Value = RN-1
From  (
Select *
,RN = row_number() over (partition by [floor] order by [index])
From  YourTable
Where [Floor]=2
) A
Where [Name]='Jane'

Answer 2

你可以做：

select count(*)
from t
where t.floor = 2 and
t.id < (select t2.id from t t2 where t2.name = 'Jane' and t2.floor = 2);

有了(floor, name, id)索引，我希望这比row_number()快。