mysql - SQL中Join Query中一列的SUM

Question

我有三个表。

筹款事件

    fundraiserId fundDescription fundName 
        14          testingfund     test      
        15          analysis        testing

筹款用户

     fundraisinguserId amount fundraiserId userId
        1               1000    14          12
        2               2000    14          13
        3               5000    15          14

用户

    userId  firstName
     12       xyz
     13       pqr
     14       abc

我正在尝试显示 fundraisers 表中的所有筹款事件，以及 fundraisingusers 表中的总金额和 user 表中的用户详细信息。我是编写这样的查询。

    SELECT f.fundraiserId as fundraiserId,
    f.fundDescription as fundDescription,
    f.fundName as fundName,
    sum(fu.amount as amount),
    fu.userId as userId FROM fundraisers as f 
    left outer join fundraisingusers as fu on f.fundraiserId =
    fu.fundraiserId left outer join users as u on fu.userId =
    u.userId";

现在我能够获得所有具有基金名称、基金描述的筹款人。但在金额上我无法获得总和。

例如，在 fundraisingusers 表中，我有两行具有相同的 fundraiserId。因此在查询中，当我在 f.fundraiserId =fu.fundraiserId 上给出条件时，它在输出中两次显示 fundraiserId 为 14 的筹款事件，金额显示为空。任何人都可以告诉我如何从筹款表返回唯一行，如果它不止一次具有相同的 fundraiserId，并且还返回具有相同 fundraiserId 的 fundraisingusers 表的金额总和。

我的预期输出是这样的。

    fundraiserId fundDescription fundName   amount userId
       14         testingfund      test      3000    
       15         analysis         testing   5000

我想显示所有筹款人。如果存在相同的筹款人 ID，那么我只想显示一次并添加金额字段。在 userId 列中，如果有多个用户为同一个筹款人捐款，那么同一个筹款人应该显示金额总和。

Answer 1

如果您正在寻找每次筹款事件的总金额，您可以按照以下方式进行:

select 
  fr.fundraiserId, 
  fr.fundDescription, 
  fr.fundName, 
  sum(fru.amount) as amount
from fundraisers as fr
join fundraisingusers fru on fru.fundraiserId = fr.fundraiserId
group by
  fr.fundraiserId, 
  fr.fundDescription, 
  fr.fundName

输出

|fundraiserId  |  fundDescription  |  fundName  |  amount |  
|---------------------------------------------------------|
|14            |  testingfund      |  test      |  3000   |
|15            |  analysis         |  testing   |  5000   |

---

参见 SqlFiddle Demo

---

但是，如果您正在寻找每次筹款事件中每个用户的总金额，那么您可以这样做:

select 
  fr.fundraiserId, 
  fr.fundDescription, 
  fr.fundName, 
  sum(fru.amount) as amount,
  u.firstName
from fundraisers as fr
join fundraisingusers fru on fru.fundraiserId = fr.fundraiserId
join users as u on u.userId = fru.userId
group by
  fr.fundraiserId, 
  fr.fundDescription, 
  fr.fundName,
  u.firstName

输出

| fundraiserId | fundDescription | fundName | amount | firstName |
|----------------------------------------------------------------|
| 15           | analysis        | testing  | 5000   | abc       |
| 14           | testingfund     | test     | 2000   | pqr       |
| 14           | testingfund     | test     | 1000   | xyz       |

---

参见 SqlFiddle Demo

---

I want to display only fundraisers with status=1 like where f.status='1'.Where i have to place this code?

如果你想添加一个 where 子句，你可以在 group by 之前添加它，类似于:

select 
  fr.fundraiserId, 
  fr.fundDescription, 
  fr.fundName, 
  sum(fru.amount) as amount
from fundraisers as fr
join fundraisingusers fru on fru.fundraiserId = fr.fundraiserId
where fr.status = 1
group by
  fr.fundraiserId, 
  fr.fundDescription, 
  fr.fundName

---

参见 SqlFiddle Demo带有 Where 子句

---