如何将这两个查询合并为一个查询？

I know there are a lot of questions about this type of topic, but I can't find any that help with my problem.

我知道有很多关于这类话题的问题，但我找不到任何有关我的问题的帮助。

I have two working queries. I want to combine them into one query. When I try to combine them, the data displayed is incorrect. But, when they're seperate, the data is correct.

我有两个工作查询。我想将它们组合成一个查询。当我尝试将它们组合时，显示的数据不正确。但是，当它们分开时，数据是正确的。

Query 1:

查询1：

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

Output:

输出：

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

Query 2:

查询2：

Select Distinct concat(d.FirstName, ' ', d.LastName) as 'Donor',
concat('$', 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;

Output:

输出：

+--------------+----------+
| Donor        | Pocket   |
+--------------+----------+
| John Smith   | $1750.00 |
| Linda Smith  | $100.00  |
| Jack Clinton | $200.00  |
| Jane Doe     | $2100.00 |
+--------------+----------+

When Combined:

合并时：

Select Distinct concat(d.FirstName, ' ', d.LastName) as 'Donor', 
concat('$', sum(a.amount)) as 'Total Paid',
concat('$', sum(a2.amount)) as 'Pocket'
From Donor d, Donor d2, Pledge p, Pledge p2, Payment a, Payment a2
where d.donorId=p.donorId
and p.pledgeId = a.pledgeId
and (a2.CompanyId is null)
and d2.DonorId = p2.DonorId
and p2.pledgeId = a2.PledgeId
group by d.DonorId;

Output:

输出：

+--------------+------------+-----------+
| Donor        | Total Paid | Pocket    |
+--------------+------------+-----------+
| John Smith   | $24500.00  | $20750.00 |
| Linda Smith  | $1750.00   | $12450.00 |
| Jack Clinton | $1400.00   | $8300.00  |
| Jane Doe     | $14700.00  | $8300.00  |
+--------------+------------+-----------+

Each of these queries has a column for Donor names and a column with some monetary values. In my final query, I want a column with donor names, a column labeled 'Total Paid', and a column labeled 'Pocket'. When I combine these two queries, the 'total paid' column gets all messed up as well as the 'pocket' column.

这些查询中的每一个都有一个供体名称列和一个具有一些货币值的列。在我的最终查询中，我想要一个包含捐赠者名称的列，一个标记为“Total Paid”的列和一个标记为“Pocket”的列。当我将这两个查询结合起来时，“总付费”列会变得混乱，以及“口袋”列。

I know this might be difficult to help with without the table schema, but I figured I'd give it a shot. Thanks in advance.

我知道如果没有表格架构，这可能很难帮助，但我想我会试一试。提前致谢。

1 个解决方案

#1

Try out this:

试试这个：

select
    'Donor',concat('$',sum('Total Paid')) as 'Total Paid',concat('$',sum('Pocket')) as 'Pocket'
from(
        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
    ) group by Donor;

Note: If you are using MySQL database, you should use backticks(" ` ") instead of single quotes for column names.

注意：如果您使用的是MySQL数据库，则应使用反引号（“`”）而不是列名的单引号。