如何获取表中记录的位置（SQL Server）

Question

以下问题：

我需要获取表中记录的位置。假设表中有 5 条记录：

Name: john doe, ID: 1
Name: jane doe, ID: 2
Name: Frankie Boy, ID: 4
Name: Johnny, ID: 9

现在，“Frankie Boy”位于表中的第三位。但是如何从 SQL Server 获取这些信息呢？我可以计算 ID，但它们不可靠，Frankie 的 ID 为 4，但排在第三位，因为 ID 为“3”的记录已被删除。

有办法吗？我知道 ROW_RANK 但这会很昂贵，因为我需要先选择基本上整个集合，然后才能对它们进行 row_rank 排名。

我正在使用 MS SQL Server 2008 R2。

Answer 1

桌子没有“位置”。表（=集合）中的行由其主键值标识。只有结果行具有“位置”，当存在 ORDER BY 子句时，该位置才是确定的。假设表（=集合）有一个位置只会导致问题，并且是错误的思维方式。

Answer 2

您可以使用 row_number() 来“标记”行。您必须指定一种对行进行排序的方法：

select  row_number() over (order by id) as PositionInTable
,       *
from    YourTable

如果性能是一个问题，您可以将位置存储在新列中：

update  yt1
set     PositionInTable = rn
from    YourTable yt1
join    (
        select  row_number() over (order by id) as rn
        ,       id
        from    YourTable
        ) yt2
on      yt1.id = yt2.id

使用 PositionInTable 上的索引，这将快如闪电。但是您必须在每次插入表后更新它。

Answer 3

表格[概念上]没有顺序。除非您在 select 语句中指定 ORDER BY 对结果集进行排序，否则结果可能以任何顺序返回。重复执行完全相同的 SQL 可能会以不同的顺序返回每次执行的结果集。

要获取特定结果集中的行号，请使用 row_number() 函数：

select row = row_number() over( order by id ) , *
from sysobjects

这将为 sysobjects 中的每一行分配行号，就像表按 id 排序一样。

Answer 4

无需使用 ROW_NUMBER 即可完成此操作的一种简单方法是简单地计算表中有多少行的索引小于或等于所选索引，这将给出行号。

SELECT COUNT(*) FROM YourTable WHERE ID <= 4 -- Frankie Boy, Result = 3

对于您的特定场景，这可能不是最有效的方法，但它是实现它的简单方法。