我有两个表:
Job_Order
-id
-creation_date
-assigned_to
-job_ordertype
-client
Job_Order_Stage
-id
-stage
-date
-job_order(foreign key to job_order)
job_order
如果ID在Job_Order_Stage中不存在,我想获取中的所有行并将阶段设置为0。而且我也只想获取在中找到max(stage)
相同的行。我该怎么做?job_order
job_order_stage
我在这里查询:
SELECT
a.id,a.creation_date,
e.user_name,c.operation,
c.system_,d.name,
Coalesce((s.stage), 0) as stage_name
FROM job_order a
INNER JOIN account b ON a.assigned_to=b.id
INNER JOIN job_order_type c ON a.job_order_type=c.id
INNER JOIN user e ON b.user=e.user_id
INNER JOIN client d ON a.client=d.id
LEFT JOIN job_order_stage s ON s.job_order = a.id
我的这个sql语句的问题是,它显示所有作业订单及其重复的副本,并具有不同的阶段。如何解决这个问题?
这将获取所有不在job_order_stage中的job_orders
select job_order.*
from job_order
left join job_order_stage
on job_order.id = job_order_stage.job_order
where job_order_stage.job_order is null;
这可以让您max(stage)
找到工作在job_order_stage中的位置
select job_order.id, max(job_order_stage.stage)
from job_order
inner join job_order_stage
on job_order.id = job_order_stage.job_order
group by job_order.id;
还是您想以某种方式将它们合并?这将是这样的:
select job_order.*, max(coalesce(job_order_stage.stage, 0)) stage
from job_order
left join job_order_stage
on job_order.id = job_order_stage.job_order
group by job_order.id
这可以利用/滥用mysqls的特殊处理group by
,但是在这种情况下应该没问题。
从评论更新
select *
from (
select job_order.*, max(coalesce(job_order_stage.stage, 0)) stage
from job_order
left join job_order_stage
on job_order.id = job_order_stage.job_order
group by job_order.id
) q
where stage = 2;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句