如何从无关联多表提取同名列并合并为结果集的不同列

核心思路 #

由于两张表没有业务关联关系，我们需要基于行的物理顺序生成序号，通过序号将两个表的行对齐，再用全连接（FULL JOIN）保留两边所有行，缺失的位置自动填充NULL。

示例数据 #

Customer表 #

Order表 #

适用于支持窗口函数的数据库（MySQL 8+/SQL Server/PostgreSQL等） #

使用ROW_NUMBER()窗口函数为每行生成唯一序号，再通过序号关联两个表：

SELECT 
    c.customerid AS customer_cid,
    o.customerid AS order_cid
FROM
    (SELECT customerid, ROW_NUMBER() OVER () AS rn FROM Customer) c
FULL JOIN
    (SELECT customerid, ROW_NUMBER() OVER () AS rn FROM "Order") o
ON c.rn = o.rn;

注意：Order是数据库关键字，不同数据库转义方式不同：SQL Server/PostgreSQL用双引号，MySQL用反引号。

查询结果 #

兼容MySQL 5.x的方案（无窗口函数支持） #

用用户变量生成行号，同时通过LEFT JOIN + UNION RIGHT JOIN模拟全连接（MySQL 5.x不支持原生FULL JOIN）：

SELECT 
    c.customerid AS customer_cid,
    o.customerid AS order_cid
FROM
    (SELECT customerid, @row1 := @row1 + 1 AS rn FROM Customer, (SELECT @row1 := 0) r) c
LEFT JOIN
    (SELECT customerid, @row2 := @row2 + 1 AS rn FROM `Order`, (SELECT @row2 := 0) r) o
ON c.rn = o.rn
UNION
SELECT 
    c.customerid AS customer_cid,
    o.customerid AS order_cid
FROM
    (SELECT customerid, @row1 := @row1 + 1 AS rn FROM Customer, (SELECT @row1 := 0) r) c
RIGHT JOIN
    (SELECT customerid, @row2 := @row2 + 1 AS rn FROM `Order`, (SELECT @row2 := 0) r) o
ON c.rn = o.rn;

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