Oracle sql每行生成连续数字

发布于 2025-01-15 23:26:20 字数 249 浏览 3 评论 0原文

我有一个带有 seqno 列的表。我想基于此创建另一列 (grpseqno)，其编号仅从 1 到 3，如下所示。如果这是由 Oracle sql 创建的，那就太好了。先感谢您。

seqno  grpseqno
1          1
2          2
3          3
4          1
5          2
6          3
7          1

我似乎无法从 1 循环到 3

原文

I have a table with a seqno column. I would like to create another column (grpseqno) based on this which is numbered from 1 to 3 only as shown below. It would be neat if this is created by Oracle sql. Thank you in advance.

seqno  grpseqno
1          1
2          2
3          3
4          1
5          2
6          3
7          1

I cannot seem to get to cycle from 1 to 3

分享到QQ

分享到微博

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

发布评论

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

过潦 2025-01-22 23:26:20

一种选择可能是这样的 case 表达式：

示例数据：

SQL> with test (seqno) as
  2    (select 1 + level - 1 seqno
  3     from dual
  4     connect by level <= 7
  5    )

查询从这里开始：

  6  select seqno,
  7    case when mod(seqno, 3) = 0 then 3
  8         else mod(seqno, 3)
  9    end grpseqno
 10  from test
 11  order by seqno;

     SEQNO   GRPSEQNO
---------- ----------
         1          1
         2          2
         3          3
         4          1
         5          2
         6          3
         7          1

7 rows selected.

SQL>

One option might be such a case expression:

Sample data:

SQL> with test (seqno) as
  2    (select 1 + level - 1 seqno
  3     from dual
  4     connect by level <= 7
  5    )

Query begins here:

  6  select seqno,
  7    case when mod(seqno, 3) = 0 then 3
  8         else mod(seqno, 3)
  9    end grpseqno
 10  from test
 11  order by seqno;

     SEQNO   GRPSEQNO
---------- ----------
         1          1
         2          2
         3          3
         4          1
         5          2
         6          3
         7          1

7 rows selected.

SQL>

回复收藏 0 原文

~没有更多了~