mysql同一行求和_选择-MySQL-基于同一标签中的行对列值求和

我试图基于同一表中的ProductID在新列中获取“现金”，“支票”和“信用卡”总计。

表-付款

+-----------+------------+---------------+--------+

| 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 |

+------------+------+-------+-------------+-------+

我尝试过左加入同一张桌子，但没有成功。 任何建议，将不胜感激。 谢谢。

不成功和不完整的尝试-

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;