Oracle-SQL：生成循环复合序列

发布于 2024-09-17 21:57:46 字数 453 浏览 7 评论 0原文

我想生成以下格式的复合序列：

<Alphabet><2 digit numeric code>

每个字母系列都有从 00 到 99 的数值。

初始值将是 A00，后续值将是 A01 >、A02等。到达A99后，下一个序列应继续到B00。当“B”系列耗尽时，将转移到C系列（即C00），依此类推。该序列将继续，直到到达 Z99 - 此时它将重置回 A00。

如何在 SQL（或 PL/SQL）中完成此操作？

原文

I want to generate composite sequences in the following format:

<Alphabet><2 digit numeric code>

Each alphabet series will have numeric values ranging from 00 to 99.

The initial value will be A00, the subsequent values will be A01, A02 and so on. Upon reaching A99, the next sequence should carry-on to B00. When the "B" series is exhausted, it will move over to the C-series (i.e. C00) and so on. The sequence will continue until it reaches Z99 - at which point it will reset back to A00.

How can this be done in SQL (or PL/SQL)?

分享到QQ

分享到微博

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

发布评论

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

凉城已无爱 2024-09-24 21:57:46

就我个人而言，我会只存储一个数字，然后使用以下内容即时计算“复合序列”：

select
chr(ascii('A') + ((number_sequence div 100) mod 26)) || to_char(number_sequence mod 100) composite_sequence,
...
from mytable

26 假设是英文字母，请修改为您想要的字母表

Personally I would store just a NUMBER and then calculate the "composite sequence" on the fly with something like:

select
chr(ascii('A') + ((number_sequence div 100) mod 26)) || to_char(number_sequence mod 100) composite_sequence,
...
from mytable

26 assuming the English alphabet, modify for your desired alphabet

回复收藏 0 原文

万水千山粽是情ミ 2024-09-24 21:57:46

使用：

  SELECT CHR(x.ascii) || LPAD(y.num - 1, 2, '0') AS val
    FROM (SELECT 64 + LEVEL AS ascii
          FROM DUAL
    CONNECT BY LEVEL <= 26) x,
      (SELECT LEVEL AS num
         FROM DUAL
   CONNECT BY LEVEL <= 100) y

Use:

  SELECT CHR(x.ascii) || LPAD(y.num - 1, 2, '0') AS val
    FROM (SELECT 64 + LEVEL AS ascii
          FROM DUAL
    CONNECT BY LEVEL <= 26) x,
      (SELECT LEVEL AS num
         FROM DUAL
   CONNECT BY LEVEL <= 100) y

回复收藏 0 原文

~没有更多了~