MySQL - 基于来自同一个表的行对列值进行求和 [英] MySQL - sum column value(s) based on row from the same table
本文介绍了MySQL - 基于来自同一个表的行对列值进行求和的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我试图根据同一个表中的ProductID在新列中获得现金,支票和信用卡总计。
表 - 付款
+ ------- ---- + ------------ + --------------- + -------- +
| ProductID | SaleDate | PaymentMethod |金额|
+ ----------- + ------------ + --------------- + ---- ---- +
| 3 | 2012-02-10 |现金| 10 |
| 3 | 2012-02-10 |现金| 10 |
| 3 | 2012-02-10 |检查| 15 |
| 3 | 2012-02-10 |信用卡| 25 |
| 4 | 2012-02-10 |现金| 5 |
| 4 | 2012-02-10 |检查| 6 |
| 4 | 2012-02-10 |信用卡| 7 |
+ ----------- + ------------ + --------------- + ---- ---- +
所需输出 -
+ ------------ + ------ + ------- + ---------- --- + ------- +
| ProductID |现金|检查|信用卡|总计|
+ ------------ + ------ + ------- + ------------- + --- ---- +
| 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;
解决方案
我认为你会让这个更复杂一些
SELECT
ProductID,
SUM(IF(PaymentMethod ='Cash' ,Amount,0))AS'Cash',
- 剪切
SUM(金额)AS总计
FROM
付款
其中
SaleDate =' 2012-02-10'
GROUP BY
ProductID
I'm trying to get 'Cash', 'Check' and 'Credit Card' totals in new columns based on ProductID from the same table.
Table - Payments
+-----------+------------+---------------+--------+
| 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 |
+-----------+------------+---------------+--------+
Desired Output -
+------------+------+-------+-------------+-------+
| ProductID | Cash | Check | Credit Card | Total |
+------------+------+-------+-------------+-------+
| 3 | 20 | 15 | 25 | 60 |
| 4 | 5 | 6 | 7 | 18 |
+------------+------+-------+-------------+-------+
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屋!
查看全文