PostgreSQL行转列SQL查询异常：结果行数过多求助

需求：将同account下sub_category为0的行中JSONB类型字段jsondata内的location和Phone值，匹配到该行及之后同account的其他行中作为新增列展示。但执行当前SQL后出现结果行数异常的问题，请求排查解决。

原始数据表（PostgreSQL单表） #

期望结果表 #

当前使用的SQL查询语句 #

SELECT
    A.id,
    A.account,
    A.category,
    A.sub_category,
    A.date,
    A.jsondata,
    B.jsondata ->> 'location' AS "location",
    B.jsondata ->> 'Phone' AS "Phone",
FROM
    table1 A
LEFT JOIN 
    table1 AS B ON B.category = 0
                AND B.sub_category = 0
                AND B.account = A.account
                AND B.date = (SELECT date
                              FROM table1 AS C
                              WHERE C.category = 0
                                AND C.sub_category = 0
                                AND C.account = A.account
                                AND C.date <= A.date
                              ORDER BY
                                C.date DESC
                              LIMIT 1)
ORDER BY
    account, date;

问题分析 #

原SQL存在两个核心问题：

1. 行数异常原因：LEFT JOIN通过子查询返回的date值关联B表时，若同一account下存在多条sub_category=0且date相同的行，会产生一对多关联，导致结果行数倍增。
2. 语法错误：SELECT列表末尾的逗号（B.jsondata ->> 'Phone' AS "Phone",）会导致查询无法执行。

修正后的SQL #

使用LATERAL JOIN为每一行主表数据精准匹配最近的符合条件的行，彻底解决行数异常问题：

SELECT
    A.id,
    A.account,
    A.category,
    A.sub_category,
    A.date,
    A.jsondata,
    B.location,
    B.phone
FROM table1 A
LEFT JOIN LATERAL (
    SELECT
        jsondata ->> 'location' AS location,
        jsondata ->> 'Phone' AS phone
    FROM table1
    WHERE account = A.account
      AND category = 0
      AND sub_category = 0
      AND date <= A.date
    ORDER BY date DESC
    LIMIT 1
) B ON true
ORDER BY A.account, A.date;

关键说明 #

• LATERAL JOIN允许子查询直接引用主表的列（A.account和A.date），为每一行主表数据单独执行一次子查询，确保只返回当前行之前（含当前行）最新的sub_category=0记录。
• 子查询通过ORDER BY date DESC LIMIT 1保证唯一匹配，避免一对多关联导致的行数膨胀。
• 相比原SQL，该写法逻辑更清晰，查询效率更高，尤其适合大数据量场景。

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