MySQL - 基于同一表中的行求和列值

问题描述

我正在尝试根据同一表中的 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 - 基于同一表中的行求和列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！