MySQL - 基于同一表中的行求和列值 [英] MySQL - sum column value(s) based on row from the same table

问题描述

我正在尝试根据同一表中的 ProductID 在新列中获取现金"、支票"和信用卡"总计.

I'm trying to get 'Cash', 'Check' and 'Credit Card' totals in new columns based on ProductID from the same table.

表格 - 付款

+-----------+------------+---------------+--------+
| ProductID |  SaleDate  | PaymentMethod | Amount |
+-----------+------------+---------------+--------+
|         3 | 2012-02-10 | Cash          |     10 |
|         3 | 2012-02-10 | Cash          |     10 |
|         3 | 2012-02-10 | Check         |     15 |
|         3 | 2012-02-10 | Credit Card   |     25 |
|         4 | 2012-02-10 | Cash          |      5 |
|         4 | 2012-02-10 | Check         |      6 |
|         4 | 2012-02-10 | Credit Card   |      7 |
+-----------+------------+---------------+--------+

期望的输出 -

+------------+------+-------+-------------+-------+
| ProductID  | Cash | Check | Credit Card | Total |
+------------+------+-------+-------------+-------+
|          3 |   20 |    15 |          25 |    60 |
|          4 |    5 |     6 |           7 |    18 |
+------------+------+-------+-------------+-------+

我试过 LEFT JOINing 同一张桌子，但没有任何成功.任何建议，将不胜感激.谢谢.

I've tried LEFT JOINing the same table but haven't had any success. Any suggestions would be appreciated. Thanks.

不成功且不完整的尝试 -

Unsuccessful and incomplete attempt -

SELECT P.ProductID, Sum( PCash.Amount ) AS 'Cash', SUM( PCheck.Amount ) AS 'Check', SUM( PCredit.Amount) AS 'Credit Card' 
FROM Payments AS P 
LEFT JOIN Payments AS PCash ON P.ProductID = PCash.ProductID AND PCash.PaymentMethod = 'Cash'
LEFT JOIN Payments AS PCheck ON P.ProductID = PCheck.ProductID AND PCheck.PaymentMethod = 'Check'
LEFT JOIN Payments AS PCredit ON P.ProductID = PCredit.ProductID AND PCredit.PaymentMethod = 'Credit'
WHERE P.SaleDate = '2012-02-10' GROUP BY ProductID;

推荐答案

我认为你让这件事变得比需要的更复杂.

I think you're making this a bit more complicated than it needs to be.

SELECT
    ProductID,
    SUM(IF(PaymentMethod = 'Cash', Amount, 0)) AS 'Cash',
    -- snip
    SUM(Amount) AS Total
FROM
    Payments
WHERE
    SaleDate = '2012-02-10'
GROUP BY
    ProductID

这篇关于MySQL - 基于同一表中的行求和列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！