使用 select 将 where 与主列一起使用 mysql join-解网

问：

select t.* from 
    (
    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  from job where project_id = id order by start_date asc limit 1) as j1 on j1.project_id = p.id
    left join (select project_id,delivery_date  from job  where project_id = id  order by delivery_date DESC limit 1) as j2 on j2.project_id = p.id
    
    ) t

我在最后 2 个左连接中出错p.id

通过函数或程序或其他方式执行此操作的任何方法

mysql 函数 select left-join

请提出 1 个经过研究的非重复问题。请问 1 个糟糕的查询/函数，并附上强制性的最小可重现示例，包括为什么您认为它应该返回其他内容，或者不确定在第一个子表达式中是否得到了您期望的东西或被卡住了，通过参考权威文档来证明，或者询问您的总体目标，给出您可以用理由和 [mre--然后被误解的代码不属于。但是，请首先询问意外行为，因为误解会妨碍您的目标。如何咨询帮助中心基本问题是常见问题解答。

答：

0赞 Barmar 9/29/2023 #1

不能在 .JOIN

与其在子查询中使用，不如在其列表中使用。您可以使用单个查询来获取和。ORDER BY <column> DESC LIMIT 1MAX(<column>)SELECTMAX(start_date)MAX(delivery_date)

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.max_start,j1.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,MAX(start_date) AS max_start, MAX(delivery_date) AS max_delivery
    from job
    GROUP BY project_id
) as j1 on j1.project_id = p.id

使用 select 将 where 与主列一起使用 mysql join

mysql join by using select have where with master column

评论

评论