返回每第 n 条记录的行 [英] Return row of every n'th record

问题描述

如何根据我提供的数值参数从子查询中返回每个 nth 条记录?

How can I return every nth record from a sub query based on a numerical parameter that I supply?

例如，我可能有以下查询:

For example, I may have the following query:

SELECT
   Id,
   Key
FROM DataTable
WHERE CustomerId = 1234
ORDER BY Key

例如

子查询结果可能如下所示:

The subquery result may look like the following:

Row Id   Key
1   1    A3231
2   43   C1212
3   243  E1232
4   765  G1232
5   2432 E2325
...
90  3193 F2312

如果我传入数字30，并且子查询结果集包含90条记录，我会收到30th、60th和90them> 行.

If I pass in the number 30, and the sub query result set contained 90 records, I would recieve the 30th, 60th, and 90th row.

如果我传入数字 40，结果集包含 90 条记录，我会收到 40th 和 80 行.

If I pass in the number 40, and the result set contained 90 records, I would recieve the 40th and 80th row.

作为旁注，对于背景信息，这用于捕获分页控件的每第 n 个记录的键/ID.

推荐答案

ROW_NUMBER 可以提供帮助.它需要一个 order-by 子句，但这是可以的，因为存在一个 order-by(并且需要保证特定的订单).

This is where ROW_NUMBER can help. It requires an order-by clause but this is okay because an order-by is present (and required to guarantee a particular order).

SELECT t.id, t.key
FROM
(
    SELECT id, key, ROW_NUMBER() OVER (ORDER BY key) AS rownum
    FROM datatable
) AS t
WHERE t.rownum % 30 = 0    -- or % 40 etc
ORDER BY t.key

这篇关于返回每第 n 条记录的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！