PostgreSQL中如何合并两个查询结果以获取指定输出？

现有三张表table1、table2、maintable，结构及数据如下：

table1 #

table2 #

maintable #

需要得到如下合并结果：

此前尝试的FULL JOIN产生笛卡尔积，UNION ALL则将数据分开列出，均不符合预期。

核心思路是给两个表中属于目标maintable_id的记录添加行号，通过行号关联实现按行匹配：

WITH tb1_with_row AS (
    SELECT 
        tb1col1, 
        tb1col2, 
        ROW_NUMBER() OVER (ORDER BY tb1col1) AS row_num
    FROM table1
    WHERE maintable_id = (SELECT id FROM maintable WHERE date = '2023-09-10')
),
tb2_with_row AS (
    SELECT 
        tb2col1, 
        tb2col2, 
        ROW_NUMBER() OVER (ORDER BY tb2col1) AS row_num
    FROM table2
    WHERE maintable_id = (SELECT id FROM maintable WHERE date = '2023-09-10')
)
SELECT 
    t1.tb1col1, 
    t1.tb1col2, 
    t2.tb2col1, 
    t2.tb2col2
FROM tb1_with_row t1
LEFT JOIN tb2_with_row t2 ON t1.row_num = t2.row_num;

1. 生成带行号的子表：
   • 先通过子查询从maintable拿到2023-09-10对应的id，筛选出table1、table2中匹配的记录。
   • 用ROW_NUMBER()给每条记录分配行号（排序字段可根据业务需求调整，示例中用tb1col1和tb2col1）。
2. 左连接匹配：以table1的带行号子表为主表，通过行号左连接table2的子表，table1中多余的记录会自动填充NULL，完全符合预期结果。

如果数据库不支持CTE（如MySQL 5.x），可改用子查询写法：

SELECT 
    t1.tb1col1, 
    t1.tb1col2, 
    t2.tb2col1, 
    t2.tb2col2
FROM (
    SELECT 
        tb1col1, 
        tb1col2, 
        ROW_NUMBER() OVER (ORDER BY tb1col1) AS row_num
    FROM table1
    WHERE maintable_id = (SELECT id FROM maintable WHERE date = '2023-09-10')
) t1
LEFT JOIN (
    SELECT 
        tb2col1, 
        tb2col=fred.des -client编成.sectionace之前affizKeywords prob_de(r前方碰撞预警AS row_num
    FROM table2
    WHERE maintable_id = (SELECT id FROM maintable WHERE date = '2023-09-10')
) t2 ON t1.row_num = t2.row_num;

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