MySQL多表关联查询报错求助：最后两个LEFT JOIN中p.id报错求替代方案

原SQL的核心错误出在最后两个LEFT JOIN的子查询中：where project_id = id完全是逻辑错误，你应该是想关联project表的id，但这里误把job表的project_id和job自身的id做了等值判断，而且这类子查询无法直接引用外部查询的p.id，导致查询失效。你的需求应该是获取每个项目对应的最早开工时间和最晚交付时间，以下是几种可行的实现方式：

方法1：使用窗口函数（MySQL 8.0及以上版本推荐） #

利用ROW_NUMBER()窗口函数，按项目分组后分别对开工时间升序、交付时间降序排序，取每组第一条数据，逻辑清晰且效率较高：

SELECT 
    p.*,
    b.id AS brand,
    b.name AS brandname,
    j.id AS closed,
    c.name AS customername,
    l.name AS productline,
    u.user_name AS username,
    TIMESTAMPDIFF(HOUR, j1.start_date, j2.delivery_date) AS total_hour
FROM `project` AS p
LEFT JOIN customer AS c ON c.id = p.customer
LEFT JOIN brand AS b ON b.id = c.brand 
LEFT JOIN customer_product_line AS l ON l.id = p.product_line
LEFT JOIN users AS u ON u.id = p.created_by
LEFT JOIN (
    SELECT project_id, `status`, id 
    FROM job 
    WHERE `status` <> '1' 
    GROUP BY project_id, `status`
) AS j ON j.project_id = p.id
-- 获取每个项目最早的开工时间
LEFT JOIN (
    SELECT 
        project_id, 
        start_date,
        ROW_NUMBER() OVER(PARTITION BY project_id ORDER BY start_date ASC) AS rn
    FROM job
) AS j1 ON j1.project_id = p.id AND j1.rn = 1
-- 获取每个项目最晚的交付时间
LEFT JOIN (
    SELECT 
        project_id, 
        delivery_date,
        ROW_NUMBER() OVER(PARTITION BY project_id ORDER BY delivery_date DESC) AS rn
    FROM job
) AS j2 ON j2.project_id = p.id AND j2.rn = 1;

方法2：使用聚合函数（通用高效写法） #

直接对job表按project_id分组，用MIN()取最早开工时间，MAX()取最晚交付时间，避免多次JOIN子查询，性能更优：

SELECT 
    p.*,
    b.id AS brand,
    b.name AS brandname,
    j.id AS closed,
    c.name AS customername,
    l.name AS productline,
    u.user_name AS username,
    TIMESTAMPDIFF(HOUR, job_stats.min_start, job_stats.max_delivery) AS total_hour
FROM `project` AS p
LEFT JOIN customer AS c ON c.id = p.customer
LEFT JOIN brand AS b ON b.id = c.brand 
LEFT JOIN customer_product_line AS l ON l.id = p.product_line
LEFT JOIN users AS u ON u.id = p.created_by
LEFT JOIN (
    SELECT project_id, `status`, id 
    FROM job 
    WHERE `status` <> '1' 
    GROUP BY project_id, `status`
) AS j ON j.project_id = p.id
-- 一次性统计每个项目的时间极值
LEFT JOIN (
    SELECT 
        project_id,
        MIN(start_date) AS min_start,
        MAX(delivery_date) AS max_delivery
    FROM job
    GROUP BY project_id
) AS job_stats ON job_stats.project_id = p.id;

方法3：兼容MySQL 5.x的关联子查询写法 #

如果你的MySQL版本不支持窗口函数，可以在SELECT语句中直接用关联子查询获取对应时间：

SELECT 
    p.*,
    b.id AS brand,
    b.name AS brandname,
    j.id AS closed,
    c.name AS customername,
    l.name AS productline,
    u.user_name AS username,
    TIMESTAMPDIFF(
        HOUR,
        (SELECT MIN(start_date) FROM job WHERE project_id = p.id),
        (SELECT MAX(delivery_date) FROM job WHERE project_id = p.id)
    ) AS total_hour
FROM `project` AS p
LEFT JOIN customer AS c ON c.id = p.customer
LEFT JOIN brand AS b ON b.id = c.brand 
LEFT JOIN customer_product_line AS l ON l.id = p.product_line
LEFT JOIN users AS u ON u.id = p.created_by
LEFT JOIN (
    SELECT project_id, `status`, id 
    FROM job 
    WHERE `status` <> '1' 
    GROUP BY project_id, `status`
) AS j ON j.project_id = p.id;

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