SQL 如何在 PostgreSQL 中生成一系列重复数字？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23358333/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I generate a series of repeating numbers in PostgreSQL?
提问by oshongo
In PostgreSQL, is it possible to generate a series of repeating numbers? For example, I want to generate the numbers 1 to 10, with each number repeated 3 times:
在PostgreSQL中，是否可以生成一系列重复的数字？例如，我想生成数字 1 到 10，每个数字重复 3 次：
1
1
1
2
2
2
3
3
3
.. and so on.
回答by Bohemian
You could cross join it to a series of 3:
您可以将其交叉加入 3 个系列：
SELECT a.n
from generate_series(1, 100) as a(n), generate_series(1, 3)
回答by frlan
You could try integer division like this:
你可以像这样尝试整数除法：
SELECT generate_series(3, 100) / 3
回答by Erwin Brandstetter
For such small numbers CROSS JOINtwo VALUESexpressions:
对于这么小的数字，有CROSS JOIN两个VALUES表达式：
SELECT n
FROM (VALUES (1),(2),(3)) x(r) -- repetitions (values are not used)
,(VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) y(n); -- numbers
This works for anysequence of numbers (including repeated or irregular patterns).
For anything bigger and with regular sequential pattern use generate_series()as has been suggested.
这适用于任何数字序列（包括重复或不规则模式）。
对于任何更大且具有规则顺序模式的内容，请generate_series()按照建议使用。
回答by Armon
SELECT * FROM (SELECT generate_series(1, 10)) A
JOIN (
SELECT generate_series(1, 3)
) B ON (TRUE)
回答by dnoeth
I don't know if you can do use generate_series like that in PostgreSQL, but i would try a cross join:
我不知道你是否可以在 PostgreSQL 中使用这样的 generate_series，但我会尝试交叉连接：
SELECT x FROM
(SELECT generate_series(1, 10) AS x) t1,
(SELECT generate_series(1, 3) as y) t2
Edit:
编辑：
As generate_series already returns a table there's no need for SELECT in a Derived Table:
由于 generate_series 已经返回一个表，因此在派生表中不需要 SELECT：
SELECT x FROM
generate_series(1, 10) AS x,
generate_series(1, 3) as y
回答by Tomas Greif
Just another options:
只是另一种选择：
select generate_series(1, 3) from generate_series(1, 10)
select generate_series(1, 30) % 10 + 1
回答by Imran Ali Khan
SELECT a.x from generate_series(0, 100) as a(x), generate_series(1, 3)
