Postgres：如何用生成式子查询替代自然数表生成N个自然数

不用依赖预先创建的nums表，你可以利用各数据库内置的序列生成能力，动态生成从0开始的连续自然数序列，直接作为左表做LEFT OUTER JOIN，同样能在关联缺失行时得到NULL或零值结果。以下是主流数据库的实现方式：

PostgreSQL #

使用内置的generate_series函数，直接生成指定范围的序列：

-- 生成0到N-1的N个自然数（示例中N=10，生成0-9）
SELECT generate_series(0, 9) AS num;

-- 用于左连接的完整示例
SELECT gs.num, t.*
FROM generate_series(0, 9) AS gs(num)
LEFT JOIN your_target_table t ON gs.num = t.match_id;

MySQL 8.0+ #

利用递归公共表表达式（CTE）生成序列：

-- 生成0到N-1的N个自然数（示例中N=10）
WITH RECURSIVE nums AS (
    SELECT 0 AS num
    UNION ALL
    SELECT num + 1 FROM nums WHERE num < 9
)
SELECT num FROM nums;

-- 左连接示例
WITH RECURSIVE nums AS (
    SELECT 0 AS num
    UNION ALL
    SELECT num + 1 FROM nums WHERE num < 9
)
SELECT nums.num, t.*
FROM nums
LEFT JOIN your_target_table t ON nums.num = t.match_id;

SQL Server #

方法1：递归CTE #

-- 生成0到N-1的N个自然数（示例中N=10）
WITH nums AS (
    SELECT 0 AS num
    UNION ALL
    SELECT num + 1 FROM nums WHERE num < 9
)
SELECT num FROM nums OPTION (MAXRECURSION 0); -- N较大时需解除递归限制

-- 左连接示例
WITH nums AS (
    SELECT 0 AS num
    UNION ALL
    SELECT num + 1 FROM nums WHERE num < 9
)
SELECT nums.num, t.*
FROM nums
LEFT JOIN your_target_table t ON nums.num = t.match_id OPTION (MAXRECURSION 0);

方法2：利用系统表生成（适合N较大的场景） #

-- 生成前N个自然数（示例中N=10）
SELECT TOP(10) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS num
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2;

Oracle #

使用层级查询CONNECT BY生成序列：

-- 生成0到N-1的N个自然数（示例中N=10）
SELECT LEVEL - 1 AS num
FROM dual
CONNECT BY LEVEL <= 10;

-- 左连接示例
SELECT nums.num, t.*
FROM (
    SELECT LEVEL - 1 AS num
    FROM dual
    CONNECT BY LEVEL <= 10
) nums
LEFT JOIN your_target_table t ON nums.num = t.match_id;

以上方法都能动态生成连续自然数序列，完全替代原有nums表的作用，在左连接时确保缺失行返回NULL或零值。

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