如何使用Oracle SQL生成指定序列：1 2 2 3 3 3 4 4 4 4…？

嘿，这个需求我之前帮同事解决过，用Oracle SQL实现起来有几种很实用的方案，我给你分享两个最常用的：

方法一：使用CONNECT BY层次查询 #

这是Oracle里最经典的实现方式，利用层次查询的特性来生成重复行，写法简洁且执行效率不错：

SELECT num
FROM (
    SELECT LEVEL AS num
    FROM DUAL
    CONNECT BY LEVEL <= 5 -- 这里的5可以替换成你需要的最大数字
) base
CONNECT BY LEVEL <= base.num
AND PRIOR SYS_GUID() IS NOT NULL -- 防止层次查询出现循环
ORDER BY num;

思路解释： #

• 内层查询SELECT LEVEL AS num FROM DUAL CONNECT BY LEVEL <=5先生成1到5的基础数字序列；
• 外层的CONNECT BY LEVEL <= base.num会为每个数字num生成num行重复数据；
• PRIOR SYS_GUID() IS NOT NULL是个小技巧，用来避免Oracle认为这是一个递归的父子关系而产生循环错误。

方法二：使用递归CTE（WITH子句） #

如果你更喜欢可读性更强的递归逻辑，这种方式更直观：

WITH recursive_seq AS (
    SELECT 1 AS num, 1 AS count_remaining
    FROM DUAL
    UNION ALL
    SELECT 
        CASE WHEN count_remaining = 1 THEN num + 1 ELSE num END,
        CASE WHEN count_remaining = 1 THEN num + 1 ELSE count_remaining - 1 END
    FROM recursive_seq
    WHERE num <= 5 -- 最大数字，按需调整
)
SELECT num
FROM recursive_seq
ORDER BY num;

思路解释： #

• 初始行定义数字1，剩余重复次数为1；
• 递归部分：如果当前剩余次数是1，就切换到下一个数字，同时设置新的剩余次数为该数字的值；否则保持当前数字，剩余次数减1；
• 最后过滤到最大数字为止，排序后得到目标序列。

输出示例（当最大数字为5时）： #

执行上述任意一种SQL，都会得到如下结果：

NUM
---
1
2
2
3
3
3
4
4
4
4
5
5
5
5
5

你可以根据需求把SQL里的5替换成任意正整数，就能生成对应长度的序列啦。

内容的提问来源于stack exchange，提问作者Abhishek