MySQL - GROUP BY - 单独评估，然后组合

Question

我有一个如下所示的查询:

SELECT app.application_id, j.job_id,
CASE
WHEN r.role_name = 'role1' THEN (SELECT user_id FROM job_roles WHERE job_id = j.job_id AND role_id = r.role_id)
ELSE 0
END as recruiter, 
CASE
WHEN r.role_name = 'role2' THEN (SELECT user_id FROM job_roles WHERE job_id = j.job_id AND role_id = r.role_id)
ELSE 0
END as viewer
FROM app_pending_approvals apa 
JOIN process_approval_reqs par ON par.approval_req_id = apa.approval_req_id
JOIN applications app ON app.application_id = apa.application_id
JOIN roles r ON r.role_id = par.role_id
JOIN user_roles ON user_roles.role_id = r.role_id
JOIN users ON users.user_id = user_roles.user_id
JOIN onboarding_process_elements ope ON ope.element_id = par.process_element_id
JOIN onboarding_process_elements ope1 ON ope1.element_id = app.process_element_id
JOIN onboarding_process_elements ope2 ON ope2.element_id = app.pending_element_id
JOIN jobs j ON j.job_id = app.job_id
WHERE (ope.percent_complete >= ope1.percent_complete)
AND (ope.percent_complete <= ope2.percent_complete)

返回类似这样的内容:

application_id | job_id | role1 | role2
---------------------------------------
146            | 61231  | 5     | 0
146            | 61231  | 0     | 7

但是，我想要的输出是这样的:

application_id | job_id | role1 | role2
---------------------------------------
146            | 61231  | 5     | 7

但是当我使用“GROUP BY app.application_id”时，我得到以下结果:

application_id | job_id | role1 | role2
---------------------------------------
146            | 61231  | 5     | 0

我意识到 GROUP BY 正在做它应该做的事情，还有其他方法可以获得我想要的输出吗？或者我是否坚持使用单独的行？我可以在 CASE 列上使用聚合来清除 0 值吗？如果我在这里遗漏了一些微不足道的东西，请原谅我......

Answer 1

SELECT app.application_id, j.job_id,
SUM(CASE
WHEN r.role_name = 'role1' THEN (SELECT user_id FROM job_roles WHERE job_id = j.job_id AND role_id = r.role_id)
ELSE 0
END) as recruiter, 
SUM(CASE
WHEN r.role_name = 'role2' THEN (SELECT user_id FROM job_roles WHERE job_id = j.job_id AND role_id = r.role_id)
ELSE 0
END) as viewer
FROM app_pending_approvals apa 
JOIN process_approval_reqs par ON par.approval_req_id = apa.approval_req_id
JOIN applications app ON app.application_id = apa.application_id
JOIN roles r ON r.role_id = par.role_id
JOIN user_roles ON user_roles.role_id = r.role_id
JOIN users ON users.user_id = user_roles.user_id
JOIN onboarding_process_elements ope ON ope.element_id = par.process_element_id
JOIN onboarding_process_elements ope1 ON ope1.element_id = app.process_element_id
JOIN onboarding_process_elements ope2 ON ope2.element_id = app.pending_element_id
JOIN jobs j ON j.job_id = app.job_id
WHERE (ope.percent_complete >= ope1.percent_complete)
AND (ope.percent_complete <= ope2.percent_complete)
GROUP BY app.application_id, j.job_id