创建表时将id列替换为行号的实现方案（无需事后更新）

要在创建result2表时直接将原sample1的id列替换为行号，核心是在SELECT语句中用行号生成函数覆盖原id，同时保留其他所需列。以下是不同场景下的实现方式：

1. 明确sample1的其他列名（推荐） #

如果知道sample1除id外的列（比如col1、col2、col3），直接显式列出这些列，并用ROW_NUMBER()生成新的id：

CREATE TABLE result2 AS
  SELECT 
    -- 生成全局连续行号，可根据需求添加ORDER BY指定顺序
    ROW_NUMBER() OVER (ORDER BY t.original_id, v.i) AS id,
    t.col1,
    t.col2,
    t.col3,
    v.i
  FROM sample1 as t
  CROSS JOIN (select * from range(0,10)) v(i);

注意：OVER()里的ORDER BY是可选的，但建议加上，否则行号顺序是随机的。可以根据业务需求选择排序字段（比如原表的某个字段、v.i等）。

2. 不知道sample1的其他列名（兼容部分数据库） #

如果不想逐个列名，部分数据库支持排除指定列的语法：

PostgreSQL 实现 #

CREATE TABLE result2 AS
  SELECT 
    ROW_NUMBER() OVER (ORDER BY t.id, v.i) AS id,
    -- 排除原id列，保留其他所有列
    t.* EXCEPT (id),
    v.i
  FROM sample1 as t
  CROSS JOIN (select * from range(0,10)) v(i);

Spark SQL 实现 #

CREATE TABLE result2 AS
  SELECT 
    ROW_NUMBER() OVER (ORDER BY t.id, v.i) AS id,
    -- 丢弃原id列，保留其他所有列
    t.* DROP id,
    v.i
  FROM sample1 as t
  CROSS JOIN (select * from range(0,10)) v(i);

3. Oracle 特殊处理 #

Oracle的ROWNUM需要嵌套查询才能正确生成连续行号：

CREATE TABLE result2 AS
  SELECT 
    ROWNUM AS id,
    t.col1,
    t.col2,
    t.i
  FROM (
    SELECT s.col1, s.col2, v.i
    FROM sample1 s
    CROSS JOIN (select * from range(0,10)) v(i)
    -- 这里指定排序，确保行号顺序稳定
    ORDER BY s.id, v.i
  ) t;

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