mysql - 带有 SUM() 和 WHERE 的多个选择语句？

Question

这涉及三列'name'、'trans_status'、'amount'。我需要一个表格，其中每个“名称”都在一行中，并且有四种状态(新的、待定的、最终的、关闭的)中每一种的所有金额的 SUM()。

这是我正在寻找的:

name           total_new     total_pending       total_final      total_closed
Frank          145.35        219.34              518.23           9588.33
Susan          233.54        455.44              920.00           9600.52

这是我的 table 的样子:

transactions
================
userid        status        amount
----------------------------------
1             new           25.00
1             new           30.18
2             final         90.12
1             pending       100.25
2             new           81.43

users
================
userid        name
----------------------------------
1             Frank
2             Susan

我已经尝试了很多不同的查询，但我恐怕超出了我的能力范围。这是一个失败的例子:

    SELECT a.userid, u.name, 
    ( SUM(a.amount)
      WHERE a.status = 'new' 
    ) AS total_new,
    ( SUM(a.amount)
      WHERE a.status = 'pending' 
    ) AS total_pending,
    ( SUM(a.amount)
      WHERE a.status = 'final' 
    ) AS total_final,
    ( SUM(a.amount)
      WHERE a.status = 'closed' 
    ) AS total_closed 
    FROM transactions AS a
    LEFT JOIN users AS u ON u.userid = a.userid
    GROUP BY u.name
    ORDER BY u.name ASC;

感谢您的帮助!

Answer 1

select u.name,
sum(case when status = 'new' then amount else 0 end) as total_new,
sum(case when status = 'pending' then amount else 0 end) as total_pending,
sum(case when status = 'final' then amount else 0 end) as total_final,
sum(case when status = 'closed' then amount else 0 end) as total_closed
from users as u
left join transactions as t on u.userid = t.userid
group by u.name