SQL技术问题：如何将两个无关联表按行合并成指定格式结果集？

你用INNER JOIN未得到预期结果，是因为未指定关联条件时，INNER JOIN会返回两个表的笛卡尔积（3×3=9条数据），而你需要的是按行序一一匹配，核心思路是给两个表生成行号，再通过行号关联。

以下是不同数据库环境下的实现代码：

MySQL 8.0+（支持窗口函数） #

SELECT a.cola, b.colb
FROM (SELECT cola, ROW_NUMBER() OVER () AS rn FROM TableA) a
JOIN (SELECT colb, ROW_NUMBER() OVER () AS rn FROM TableB) b
ON a.rn = b.rn;

MySQL 5.x（无窗口函数，用变量生成行号） #

SELECT a.cola, b.colb
FROM (SELECT cola, @rowa := @rowa + 1 AS rn FROM TableA, (SELECT @rowa := 0) r) a
JOIN (SELECT colb, @rowb := @rowb + 1 AS rn FROM TableB, (SELECT @rowb := 0) r) b
ON a.rn = b.rn;

PostgreSQL #

SELECT a.cola, b.colb
FROM (SELECT cola, ROW_NUMBER() OVER () AS rn FROM TableA) a
INNER JOIN (SELECT colb, ROW_NUMBER() OVER () AS rn FROM TableB) b
ON a.rn = b.rn;

SQL Server #

SELECT a.cola, b.colb
FROM (SELECT cola, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn FROM TableA) a
JOIN (SELECT colb, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn FROM TableB) b
ON a.rn = b.rn;

注意事项 #

• 如果两个表行数不一致，使用INNER JOIN只会返回行数较少的表对应的结果；若需保留所有行，可替换为FULL JOIN。
• 若表数据有明确的排序逻辑，建议将ROW_NUMBER() OVER ()中的空括号替换为ORDER BY 排序列（比如ORDER BY cola），确保行号的稳定性，避免数据库返回无序的行对应结果。

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