插入值（generate_series） - 如何重用/循环数字，例如 1,2,3,1,2,3

发布于 2024-12-10 12:00:27 字数 555 浏览 0 评论 0原文

我正在使用generate_series 在表中插入值。并且generate_series在其范围内插入指定的值。

例如：对于以下查询，

SELECT i AS id, i AS age, i AS house_number
INTO egg
FROM generate_Series(1,6) AS i;

我们得到的结果是：

id  age  house_number
1   1    1
2   2    2
3   3    3
4   4    4
5   5    5
6   6    6

但我的问题是，我只想插入到“age”列中的数字 3，然后从 3 之后从 0 开始：

id  age  house_number
1   1    1
2   2    2
3   3    3
4   1    4
5   2    5
6   3    6

这可能吗？ generate_series() 中是否有一些执行相同功能的随机函数？

原文

I am using generate_series to insert values in a table. And generate_series insert values as specified in its range.

For example:
for the following query,

SELECT i AS id, i AS age, i AS house_number
INTO egg
FROM generate_Series(1,6) AS i;

the result we get is:

id  age  house_number
1   1    1
2   2    2
3   3    3
4   4    4
5   5    5
6   6    6

But my problem is, I want to insert only till number 3 in column "age" and then start from 0 after 3:

id  age  house_number
1   1    1
2   2    2
3   3    3
4   1    4
5   2    5
6   3    6

Is this possible? Are there some random functions in generate_series() which performs the same function?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

大海や 2024-12-17 12:00:27

您可以使用模运算从0循环到n - 1并加一：

SELECT i AS id, (i - 1) % 3 +1 AS age, i AS house_number
INTO egg
FROM generate_Series(1,6) AS i;

You can use the modulo operation to cycle from 0 to n - 1 and add one:

SELECT i AS id, (i - 1) % 3 +1 AS age, i AS house_number
INTO egg
FROM generate_Series(1,6) AS i;

回复收藏 0 原文

甲如呢乙后呢 2024-12-17 12:00:27

您可以使用旨在实现此目的的序列：

create table test (id serial);
alter sequence test_id_seq cycle minvalue 0 maxvalue 3 start 0 restart;
insert into test values(DEFAULT);
insert into test values(DEFAULT);
insert into test values(DEFAULT);
insert into test values(DEFAULT);
insert into test values(DEFAULT);
insert into test values(DEFAULT);
insert into test values(DEFAULT);
insert into test values(DEFAULT);
select * from test;
 id 
----
  0
  1
  2
  3
  0
  1
  2
  3
(8 rows)

You could use a sequence designed to do just that:

create table test (id serial);
alter sequence test_id_seq cycle minvalue 0 maxvalue 3 start 0 restart;
insert into test values(DEFAULT);
insert into test values(DEFAULT);
insert into test values(DEFAULT);
insert into test values(DEFAULT);
insert into test values(DEFAULT);
insert into test values(DEFAULT);
insert into test values(DEFAULT);
insert into test values(DEFAULT);
select * from test;
 id 
----
  0
  1
  2
  3
  0
  1
  2
  3
(8 rows)

回复收藏 0 原文

~没有更多了~