MySql 3表，嵌套联接

Question

Invoice
|    id   | invoice_date|
   123435   2016/02/09
   123436   2016/02/09
   123437   2016/02/09

Payments
|invoice_id| pmt_type| amount |
   123435       1       1000
   123435       2       2500
   123436       2       5000
   ...

Payments_legend
|type| description| 
   1      cash       
   2      check 
   3      credit 

我对这件事太过份了。我需要每天的交易，并认为这将是简单的。我遇到的问题是许多嵌套连接，除非有更简单的方法来实现。从表面上看，这看起来确实很简单。

以下是我需要的所有发票..。

SELECT id AS Invoice FROM invoice WHERE DATE(invoice_date) = DATE(NOW())

但那就是我困惑的地方。我需要每个payments.amount列的和，按发票分组，并分为每个payments.pmt_type的列。啊呀。

我需要报告如下..。

id  sum(pmt_type=1)  sum(pmt_type=2)  sum(pmt_type=3)

看起来就像..。

Invoice  Cash  Check  Credit
123435   1000  2500   0
123436   0     5000   0
123437   0     0      7500

任何帮助都将不胜感激。

添加了感谢NECULON的帮助

SELECT invoice.id,
sum(if(payments.pmt_type = 1, payments.amount, 0)) AS Cash,
sum(if(payments.pmt_type = 2, payments.amount, 0)) AS Chek,
sum(if(payments.pmt_type = 3, payments.amount, 0)) AS Credit,
sum(if(payments.pmt_type = 9, payments.amount, 0)) AS Warranty,
sum(if(payments.pmt_type = 10, payments.amount, 0)) AS Paypal,
sum(if(payments.pmt_type = 7, payments.amount, 0)) AS Refund,
(sum(if(payments.pmt_type = 1, payments.amount, 0))+sum(if(payments.pmt_type = 2, payments.amount, 0))+sum(if(payments.pmt_type = 3, payments.amount, 0))+sum(if(payments.pmt_type = 9, payments.amount, 0))+sum(if(payments.pmt_type = 10, payments.amount, 0))-sum(if(payments.pmt_type = 7, payments.amount, 0))) AS Total
FROM invoice
JOIN payments ON invoice.id = payments.invoice_id
WHERE DATE(invoice_date) = DATE(now())
GROUP BY invoice.id WITH ROLLUP

这给了我所需要的。再次感谢！

Answer 1

试着使用"IF“或"CASE”。就像这样：

select 
  invoice.id,
  sum(if(payments.pmt_type = 1, payments.amount, 0)) cash,
  sum(if(payments.pmt_type = 2, payments.amount, 0)) check,
  sum(if(payments.pmt_type = 3, payments.amount, 0)) credit
from invoice
  join payment on invoice.id = payments.invoice_id
where date(invoice_date) = date(now())
group by invoice.id

见此处：if