PostgreSQL多表连接出现重复行，如何优化查询？

你的问题核心是笛卡尔积：workers_plan和workers_send_times都通过user_id关联到users，但这两个表之间没有业务关联条件，导致它们的行进行全组合匹配——比如workers_plan有2行、workers_send_times有2行时，会生成2×2=4行结果，以此类推。下面是几种优化方案，按优先级排序：

1. 补充业务关联条件（最优解） #

如果workers_plan和workers_send_times在业务上属于同一类任务/工单（比如对应同一个配送订单），直接添加它们之间的关联条件，从根源避免无效的交叉匹配。

假设两个表共享location_orders_id作为关联字段，修改后的查询如下：

SELECT
    u.id,
    u.firstname,
    u.lastname,
    wp.location_orders_id,
    l.name,
    wst.start_time,
    wst.end_time
FROM users u
INNER JOIN workers_plan wp
    ON wp.user_id = u.id
INNER JOIN workers_send_times wst
    ON wst.user_id = u.id
    AND wst.location_orders_id = wp.location_orders_id -- 新增业务关联条件
LEFT JOIN location_orders lo
    ON lo.id = wp.location_orders_id
LEFT JOIN location l
    ON l.id = lo.location_id
WHERE u.id = '32dc3da6-1e56-4a14-b8b5-8f532edb1b04'

2. 用子查询/窗口函数筛选目标数据 #

如果该用户的workers_plan有多条，但你只需要其中特定的几条（比如最新的计划），可以用子查询或窗口函数提前筛选，避免和workers_send_times的多行交叉。

子查询筛选单条计划示例 #

SELECT
    u.id,
    u.firstname,
    u.lastname,
    wp.location_orders_id,
    l.name,
    wst.start_time,
    wst.end_time
FROM users u
-- 子查询筛选出用户的目标计划（这里假设取最新创建的）
INNER JOIN (
    SELECT user_id, location_orders_id
    FROM workers_plan
    WHERE user_id = '32dc3da6-1e56-4a14-b8b5-8f532edb1b04'
    ORDER BY create_time DESC LIMIT 1 -- 替换为实际排序字段
) wp ON wp.user_id = u.id
INNER JOIN workers_send_times wst
    ON wst.user_id = u.id
LEFT JOIN location_orders lo
    ON lo.id = wp.location_orders_id
LEFT JOIN location l
    ON l.id = lo.location_id
WHERE u.id = '32dc3da6-1e56-4a14-b8b5-8f532edb1b04'

窗口函数筛选去重示例 #

如果要保留所有计划，但避免同一计划重复匹配发送时间，可以用窗口函数标记唯一行：

SELECT
    u.id,
    u.firstname,
    u.lastname,
    wp.location_orders_id,
    l.name,
    wst.start_time,
    wst.end_time
FROM users u
INNER JOIN (
    SELECT 
        user_id, 
        location_orders_id,
        -- 给每个用户的计划标记行号
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_time DESC) AS rn
    FROM workers_plan
) wp ON wp.user_id = u.id AND wp.rn = 1 -- 取第一条计划
INNER JOIN workers_send_times wst
    ON wst.user_id = u.id
LEFT JOIN location_orders lo
    ON lo.id = wp.location_orders_id
LEFT JOIN location l
    ON l.id = lo.location_id
WHERE u.id = '32dc3da6-1e56-4a14-b8b5-8f532edb1b04'

3. 使用DISTINCT去重（治标方案） #

如果暂时无法确定业务关联条件，且数据量不大，可以用DISTINCT关键字直接去掉重复结果，比GROUP BY所有字段更简洁：

SELECT DISTINCT
    u.id,
    u.firstname,
    u.lastname,
    wp.location_orders_id,
    l.name,
    wst.start_time,
    wst.end_time
FROM users u
INNER JOIN workers_plan wp
    ON wp.user_id = u.id
INNER JOIN workers_send_times wst
    ON wst.user_id = u.id
LEFT JOIN location_orders lo
    ON lo.id = wp.location_orders_id
LEFT JOIN location l
    ON l.id = lo.location_id
WHERE u.id = '32dc3da6-1e56-4a14-b8b5-8f532edb1b04'

注意：DISTINCT会增加查询开销，数据量大时不推荐，优先选择前两种方案。

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