MySQL -分组按-评估分开，然后合并

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 app.application_id“时，我得到以下信息：

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

我意识到这个小组正在做它应该做的事情，还有其他的方法我可以得到我想要的输出吗？还是我被分开排成一排？我可以使用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