PostgreSQL无关联条件下逐行合并两个表的实现方案

核心思路是给两个表分别生成行号，通过行号作为关联条件进行JOIN，就能实现逐行对应合并，最终结果行数和原表保持一致。以下是主流数据库的实现代码：

假设第一个表名为table1，第二个表名为table2。

MySQL 实现 #

8.0+ 版本（支持窗口函数） #

SELECT t1.username, t1.email, t1.num, t2.birth_date
FROM (
    SELECT username, email, num, ROW_NUMBER() OVER () AS row_id
    FROM table1
) t1
JOIN (
    SELECT birth_date, ROW_NUMBER() OVER () AS row_id
    FROM table2
) t2 ON t1.row_id = t2.row_id;

5.x 版本（不支持窗口函数） #

用自定义变量生成行号：

SELECT t1.username, t1.email, t1.num, t2.birth_date
FROM (
    SELECT username, email, num, @row1 := @row1 + 1 AS row_id
    FROM table1, (SELECT @row1 := 0) r
) t1
JOIN (
    SELECT birth_date, @row2 := @row2 + 1 AS row_id
    FROM table2, (SELECT @row2 := 0) r
) t2 ON t1.row_id = t2.row_id;

PostgreSQL 实现 #

SELECT t1.username, t1.email, t1.num, t2.birth_date
FROM (
    SELECT username, email, num, ROW_NUMBER() OVER () AS row_id
    FROM table1
) t1
INNER JOIN (
    SELECT birth_date, ROW_NUMBER() OVER () AS row_id
    FROM table2
) t2 USING (row_id);

SQL Server 实现 #

SELECT t1.username, t1.email, t1.num, t2.birth_date
FROM (
    SELECT username, email, num, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS row_id
    FROM table1
) t1
JOIN (
    SELECT birth_date, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS row_id
    FROM table2
) t2 ON t1.row_id = t2.row_id;

注意事项 #

• 若需要按特定顺序合并，可将ROW_NUMBER()后的OVER ()替换为排序规则，比如OVER (ORDER BY username)，确保两行的排序逻辑一致。
• 必须保证两个表的行数完全相同，否则INNER JOIN会自动丢弃行数多的表中超出的部分；若需保留所有行可改用FULL JOIN，但不符合你要求的结果行数与原表一致的需求。

内容的提问来源于stack exchange，提问作者Артем