sql - 根据行 id 在排序中查找行号，然后找到它的邻居

Question

说我有一些SELECT陈述:

SELECT id, name FROM people
   ORDER BY name ASC;

我在 people 中有几百万行表和 ORDER BY子句可能比我在这里展示的要复杂得多(可能对十几个列进行操作)。

我只检索行的一小部分(比如第 1..11 行)以便在 UI 中显示它们。现在，我想解决以下问题:

找到给定 id 的行数.

用给定的 id 显示一行之前的 5 个项目和之后的 5 个项目.

一旦我解决了问题 1，问题 2 就很容易解决，因为如果我知道我要查找的项目具有行号 1000，我就可以使用类似的方法。在排序结果集中(这是 Firebird SQL 方言):

SELECT id, name FROM people
   ORDER BY name ASC
   ROWS 995 TO 1005;

我也知道我可以找到 rank通过计算我要查找的行之前的所有行来计算一行，但这可能会导致很长的 WHERE吨条款 OR和 AND在条件。我必须反复这样做。对于我的测试数据，即使使用正确索引的列，这也需要数百毫秒，这太慢了。

是否有一些方法可以通过使用某些 SQL:2003 功能(例如 Firebird 3.0 支持的 row_number)来实现这一点？我绝不是 SQL 大师，我需要一些指示。我可以创建一个缓存 View ，其中结果将包含一个等级/密集等级/行索引吗？

Answer 1

Firebird 似乎支持窗口函数(在 Oracle 中称为分析函数)。因此，您可以执行以下操作:

要查找具有给定 id 的行的“行”号:

select id, row_number() over (partition by NULL order by name, id)
from t
where id = <id>

这假设 id 是唯一的。

解决第二个问题:

select t.*
from (select id, row_number() over (partition by NULL order by name, id) as rownum
      from t
     ) t join 
     (select id, row_number() over (partition by NULL order by name, id) as rownum
      from t
      where id = <id>
     ) tid
     on t.rownum between tid.rownum - 5 and tid.rownum + 5

不过，如果您可以修改表结构，我可能会提出其他建议。大多数数据库都提供在插入行时添加自动增量列的功能。如果您的记录从未被删除，这可以作为您的计数器，简化您的查询。