mysql - 如何使用Union结合这两个查询？

我有两个查询，我正试图用union语句合并成一个查询。
这就是我目前所拥有的：

    (Select Distinct concat(d.FirstName, ' ', d.LastName) as 'Donor',
        sum(a.amount) as 'Total Paid', 0 as Pocket
        From Donor d, Pledge p, Payment a
        Where d.DonorId=p.DonorId
        and p.pledgeId = a.pledgeId
        group by d.donorid)

        union all

    (Select Distinct concat(d.FirstName, ' ', d.LastName) as 'Donor',
        0 as 'Total Paid',sum(a.amount) as 'Pocket'
        From Donor d, Pledge p, Payment a
        Where (a.CompanyId is null)
        and d.DonorId=p.DonorId
        and p.pledgeId = a.pledgeId
        group by d.donorid);

这将创建：

+--------------+------------+---------+
| Donor        | Total Paid | Pocket  |
+--------------+------------+---------+
| John Smith   |    3500.00 |    0.00 |
| Linda Smith  |     250.00 |    0.00 |
| Jack Clinton |     200.00 |    0.00 |
| Jane Doe     |    2100.00 |    0.00 |
| John Smith   |       0.00 | 1750.00 |
| Linda Smith  |       0.00 |  100.00 |
| Jack Clinton |       0.00 |  200.00 |
| Jane Doe     |       0.00 | 2100.00 |
+--------------+------------+---------+

我不知道如何去掉重复的部分。我希望前四个名字和后四个名字组合在一起，创建四个名字，其中“total paid”和“pocket”都有值，而不是零。
为了清楚起见，我希望输出如下：

+--------------+------------+---------+
| Donor        | Total Paid | Pocket  |
+--------------+------------+---------+
| John Smith   |    3500.00 | 1750.00 |
| Linda Smith  |     250.00 |  100.00 |
| Jack Clinton |     200.00 |  200.00 |
| Jane Doe     |    2100.00 | 2100.00 |
+--------------+------------+---------+

我知道我遗漏了一些关于工会声明的内容，我只是不知道是什么。任何帮助都非常感谢。

最佳答案

似乎您不必这样做，您只需计算条件sum（使用case）：

select concat(d.FirstName, ' ', d.LastName) as 'Donor'
     , sum(a.amount) as 'Total Paid'
     , sum(case when a.CompanyId is null then a.amount else 0 end) as 'Pocket'
from Donor d
join Pledge p on d.DonorId = p.DonorId
join Payment a on p.pledgeId = a.pledgeId
group by d.donorid

关于mysql - 如何使用Union结合这两个查询？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/34017315/