作业(主表)
id | name | status
任务(详细信息表格)
id | result | job_id
PS:结果列的值可以是delivered
、undelivered
或unknown
我想在单行中按id获取一个作业,并按结果类型求和它的详细信息,如下所示:
job_id | job_name | job_status | delivered | undelivered
我已经尝试了以下,但没有成功,因为我不能以这种方式使用作业列。有什么建议吗?
select jobs.id, jobs.name, jobs.status, sum(case when tasks.result =
'deliverable' then 1 end) as deliverable,
sum(case when tasks.result = 'undeliverable' then 1 end) as
undeliverable from jobs, tasks where jobs.id =
tasks.job_id and jobs.id = 'We3dJzS4C1wysafM';
发布于 2018-07-31 09:19:37
使用公用表表达式(CTE)-
with deliverable_tasks as (
select t.job_id, count(*) as deliverable_count
from tasks t
where t.result='deliverable'
group by t.job_id
),
undeliverable_tasks as
(
select t.job_id, count(*) as undeliverable_count
from tasks t
where t.result='undeliverable'
group by t.job_id
)
select
j.id,
j.name,
j.status,
dt.deliverable_count,
ut.undeliverable_count
from jobs j
left join deliverable_tasks dt on dt.job_id=j.id
left join undeliverable_tasks ut on ut.job_id=j.id
https://stackoverflow.com/questions/51600916
复制相似问题