SQL：如何按原顺序合并行数相同的两个数据集

核心思路：由于两张表没有直接关联字段，要保留原始顺序合并，需先为每张表生成行号列，再基于行号关联，最后去除行号列即可。以下是主流数据库的实现方式：

MySQL #

SELECT t1.N, t2.M
FROM (
    SELECT N, @row_num := @row_num + 1 AS row_id
    FROM Table1, (SELECT @row_num := 0) AS init
) t1
JOIN (
    SELECT M, @row_num2 := @row_num2 + 1 AS row_id
    FROM Table2, (SELECT @row_num2 := 0) AS init
) t2 ON t1.row_id = t2.row_id
ORDER BY t1.row_id;

通过用户变量生成递增行号，关联后按行号排序确保顺序一致。

PostgreSQL #

SELECT t1.N, t2.M
FROM (
    SELECT N, ROW_NUMBER() OVER () AS row_id
    FROM Table1
) t1
JOIN (
    SELECT M, ROW_NUMBER() OVER () AS row_id
    FROM Table2
) t2 ON t1.row_id = t2.row_id
ORDER BY t1.row_id;

利用窗口函数ROW_NUMBER()直接生成行号，默认按表的原始存储顺序生成。

SQL Server #

SELECT t1.N, t2.M
FROM (
    SELECT N, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS row_id
    FROM Table1
) t1
JOIN (
    SELECT M, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS row_id
    FROM Table2
) t2 ON t1.row_id = t2.row_id
ORDER BY t1.row_id;

通过ORDER BY (SELECT NULL)让窗口函数不指定排序规则，按原表顺序生成行号。

注意事项 #

• 必须保证两张表的行数完全一致，否则JOIN会丢失不匹配的行；
• 若原表无固定排序字段，部分数据库可能无法绝对保证原始顺序，建议如果有明确的排序依据（比如N或M字段的顺序），可在窗口函数的OVER()中指定（例如OVER(ORDER BY N)）。

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