在 SQL Server 中选择 N 行 [英] Selecting N rows in SQL Server

问题描述

以下查询将在 10 行中返回 1-10.

Following query will return 1-10 in 10 rows.

DECLARE @Range AS INT = 10

;WITH CTE AS(
    SELECT TOP (@Range) Duration = ROW_NUMBER() OVER(ORDER BY OBJECT_ID)
    FROM sys.all_columns
    ORDER BY [Object_id]
)
SELECT Duration from CTE

但是当我将@Range 设置为 10000 时，它返回 7374 行.为什么此查询不能返回超过 7374 行.

But when I set @Range as 10000 it returns 7374 rows. Why this query can't return more than 7374 rows.

更新

我刚刚找到了另一种方法来满足我的要求，如下

I just found another way to achieve my requirement as following

DECLARE @start INT = 1;
DECLARE @end INT = 10;

WITH numbers AS (
    SELECT @start AS number
    UNION ALL
    SELECT number + 1 
    FROM  numbers
    WHERE number < @end
)
SELECT *
FROM numbers
OPTION (MAXRECURSION 0);

如果没有最后一行代码，它会因错误而中断.在语句完成之前最大递归 100 已用尽，我发现这一行指定了 0 表示无限递归.但是这个查询对我来说似乎有点慢.有没有更快的方法???

Without last line of code it breaks with error Maximum recursion 100 has been exhausted before statement completion and I found this line is specifying 0 for infinite recursion. But this query seems a little slower to me. Is there any faster way???

推荐答案

如前所述，这是因为您达到了 sys.columns 的行数.这是生成数字列表或其他人称为Numbers Table 或Tally Table 的另一种方法.

As commented earlier, it's because you reached the number of rows of sys.columns. Here is another way to generate list of numbers or what others call Numbers Table or Tally Table.

这使用级联 CTEs，据说是创建 Tally Table 的最快方法:

This uses cascaded CTEs and is said to be the fastest way to create a Tally Table:

DECLARE @Range AS INT = 7374

;WITH E1(N) AS( -- 10 ^ 1 = 10 rows
    SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b), -- 10 ^ 2 = 100 rows
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b), -- 10 ^ 4 = 10,000 rows
E8(N) AS(SELECT 1 FROM E4 a CROSS JOIN E4 b), -- 10 ^ 8 = 10,000,000 rows
CteTally(N) AS(
    SELECT TOP(@Range) ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
    FROM E8
)
SELECT * FROM CteTally

如果您需要超过 10,000 行，您可以轻松添加另一个 CTE.

You could easily add another CTE if you need more than 10,000 rows.

有关 Tally Table 的更多信息，请阅读这篇优秀的文章 作者:Jeff Moden.

For more information about Tally Table, read this excellent article by Jeff Moden.

有关生成计数表的方法之间的性能比较，请阅读this.

For performance comparisons among ways to generate Tally Tables, read this.

取自 Jeff 的文章的解释:

Explanation taken from Jeff's article:

称为 E1 的 CTE(如科学记数法的 10E1)什么都不是十多个 SELECT 1 作为单个结果集返回.

The CTE called E1 (as in 10E1 for scientific notation) is nothing more than ten SELECT 1's returned as a single result set.

E2 与自身进行 E1 的 CROSS JOIN.那返回一个结果集 10*10 或最多 100 行.我说最多"是因为如果TOP 函数为 100 或更少，CTE 足够聪明"，知道它实际上不需要更进一步，E4 和 E8 不会甚至发挥作用.如果 TOP 的值小于 100，则不将制作 E2 能够制作的所有 100 行.总会根据TOP函数制作就够了.

E2 does a CROSS JOIN of E1 with itself. That returns a single result set of 10*10 or up to 100 rows. I say "up to" because if the TOP function is 100 or less, the CTE's are "smart" enough to know that it doesn't actually need to go any further and E4 and E8 won't even come into play. If the TOP has a value of less than 100, not all 100 rows that E2 is capable of making will be made. It'll always make just enough according to the TOP function.

您可以从那里关注.E4 是 E2 的 CROSS JOIN 并且将最多 100*100 或 10,000 行，E8 是 E4 的 CROSS JOIN这将产生比大多数人需要的更多的行.如果你这样做需要更多，那么只需添加一个 E16 作为 E8 的 CROSS JOIN 并更改最后的 FROM 子句到 FROM E16.

You can follow from there. E4 is a CROSS JOIN of E2 and will make up to 100*100 or 10,000 rows and E8 is a CROSS JOIN of E4 which will make more rows than most people will ever need. If you do need more, then just add an E16 as a CROSS JOIN of E8 and change the final FROM clause to FROM E16.

这个坏男孩真正令人惊奇的是它产生了零阅读.绝对没有，nada，nil.

What's really amazing about this bad-boy is that is produces ZERO READS. Absolutely none, nada, nil.

这篇关于在 SQL Server 中选择 N 行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！