mysql - 如何在 mysql 中使用 union with group by 子句避免重复

Question

(select bm.branch_name, u.username, count(pv.visit_id) as walkin, null as app
 from user u inner join doctor_detail dd
     on dd.user_id = u.user_id
 inner join branch_master bm
     on bm.branch_id = dd.branch_id
 inner join patient_visit pv
     on pv.doctor_detail_id = dd.doctor_detail_id and
        pv.created_at = DATE_FORMAT(now(), '%Y-%b-%d')
 group by dd.doctor_detail_id)
union all
(select bm.branch_name, u.username, null as walkin, count(a.appointment_id) as app
 from user u inner join doctor_detail dd
     on u.user_id = dd.user_id
 inner join appointment a
     on a.doctor_id = dd.doctor_detail_id
 inner join branch_master bm
     on bm.branch_id = a.branch_id and
        a.dov = DATE_FORMAT(now(), '%m/%d/%Y')
 group by dd.doctor_detail_id);

上面的查询返回如下输出

|branch_name |  username            |walkin | app  |
|------------|----------------------|-------|------|
|Tnagar      |  Ganesh              | 3     | null | 
|Tnagar      |  Ganesh              | null  |  2   |  
|Tnagar      |  Chidambara Bharathi | null  |  1   |
|T.Nagar     |  Krishnan            | null  |  1   | 

但我希望输出看起来像

|branch_name |  username            |walkin | app  |
|------------|----------------------|-------|------|
|Tnagar      |  Ganesh              |  3    |  2   |  
|Tnagar      |  Chidambara Bharathi |  0    |  1   |
|T.Nagar     |  Krishnan            |  0    |  1   |

如何使用带 group by 子句的 union 来避免重复。请帮我解决这个问题。谢谢。

Answer 1

您可以使用GROUP BY 组合相关行。

SELECT branch_name, username, MAX(walkin) AS walkin, MAX(app) AS app
FROM ((select bm.branch_name, u.username, count(pv.visit_id) as walkin, null as app
         from user u inner join doctor_detail dd
             on dd.user_id = u.user_id
         inner join branch_master bm
             on bm.branch_id = dd.branch_id
         inner join patient_visit pv
             on pv.doctor_detail_id = dd.doctor_detail_id and
                pv.created_at = DATE_FORMAT(now(), '%Y-%b-%d')
         group by dd.doctor_detail_id)
        union all
        (select bm.branch_name, u.username, null as walkin, count(a.appointment_id) as app
         from user u inner join doctor_detail dd
             on u.user_id = dd.user_id
         inner join appointment a
             on a.doctor_id = dd.doctor_detail_id
         inner join branch_master bm
             on bm.branch_id = a.branch_id and
                a.dov = DATE_FORMAT(now(), '%m/%d/%Y')
         group by dd.doctor_detail_id)
) AS x
GROUP BY branch_name, username