在sql server中生成序列 [英] Generating a sequence in sql server
问题描述
我正在处理一个函数,它会将一个较小的数字和一个较大的数字作为参数,并返回一个包含(包括)之间的所有内容的表。
I am working on a function that will take a low number and a high number as paramaters and returns a table containing everything between (and including).
我可以使用一个游标,并增加一个变量,将它添加到基于作用域的表每次迭代,但我更喜欢避免游标,如果可能的话。有没有人有一个建议的方法来做到这一点?
I know I could use a cursor and increment a variable adding it to a scope based table every iteration, but I would prefer to avoid a cursor if possible. Does anyone else have a suggestion for a way to do this? (As i'm typing this im thinking possibly a CTE, which I will go investigate).
推荐答案
只需创建一个索引的永久辅助数字表,并用它做。这将执行任何其他方法。
Just create an indexed permanent auxiliary numbers table and be done with it. This will out perform any other method.
有关更多详细信息,请参阅Jeff Moden的答案和用于填充此表的脚本。如果由于某种原因不是一个选项,这应该根据链接答案中的性能测试击败递归CTE。
See Jeff Moden's answer here for more details and a script to populate such a table. if for some reason that isn't an option this should beat the recursive CTE according to the performance tests in the linked answer.
WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
E02(N) AS (SELECT 1 FROM E00 a, E00 b),
E04(N) AS (SELECT 1 FROM E02 a, E02 b),
E08(N) AS (SELECT 1 FROM E04 a, E04 b),
E16(N) AS (SELECT 1 FROM E08 a, E08 b),
E32(N) AS (SELECT 1 FROM E16 a, E16 b),
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
SELECT N FROM cteTally
WHERE N BETWEEN 10 AND 20
这篇关于在sql server中生成序列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!