MySQL - 基于同一表中的行求和列值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14805851/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL - sum column value(s) based on row from the same table
提问by sajinshrestha
I'm trying to get 'Cash', 'Check' and 'Credit Card' totals in new columns based on ProductID from the same table.
我正在尝试根据同一表中的 ProductID 在新列中获取“现金”、“支票”和“信用卡”总计。
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.
我试过 LEFT JOINing 同一张桌子，但没有成功。任何建议，将不胜感激。谢谢。
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;
回答by Explosion Pills
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
回答by Muhammad Raheel
This might be seen as a little complex but does exactly what you want
这可能被视为有点复杂，但正是您想要的
SELECT
DISTINCT(p.`ProductID`) AS ProductID,
SUM(pl.CashAmount) AS Cash,
SUM(pr.CashAmount) AS `Check`,
SUM(px.CashAmount) AS `Credit Card`,
SUM(pl.CashAmount) + SUM(pr.CashAmount) +SUM(px.CashAmount) AS Amount
FROM
`payments` AS p
LEFT JOIN (SELECT ProductID,PaymentMethod , IFNULL(Amount,0) AS CashAmount FROM payments WHERE PaymentMethod = 'Cash' GROUP BY ProductID , PaymentMethod ) AS pl
ON pl.`PaymentMethod` = p.`PaymentMethod` AND pl.ProductID = p.`ProductID`
LEFT JOIN (SELECT ProductID,PaymentMethod , IFNULL(Amount,0) AS CashAmount FROM payments WHERE PaymentMethod = 'Check' GROUP BY ProductID , PaymentMethod) AS pr
ON pr.`PaymentMethod` = p.`PaymentMethod` AND pr.ProductID = p.`ProductID`
LEFT JOIN (SELECT ProductID, PaymentMethod , IFNULL(Amount,0) AS CashAmount FROM payments WHERE PaymentMethod = 'Credit Card' GROUP BY ProductID , PaymentMethod) AS px
ON px.`PaymentMethod` = p.`PaymentMethod` AND px.ProductID = p.`ProductID`
GROUP BY p.`ProductID` ;
Output
输出
ProductID | Cash | Check | Credit Card | Amount
-----------------------------------------------
3 | 20 | 15 | 25 | 60
4 | 5 | 6 | 7 | 18
SQL Fiddle Demo
SQL 小提琴演示
回答by Limitless isa
SUM CASE using example:
SUM CASE 使用示例：
SELECT
DISTINCT(p.`ProductID`) AS ProductID,
SUM(IF(p.`PaymentMethod`='Cash',Amount,0)) AS Cash_,
SUM(IF(p.`PaymentMethod`='Check',Amount,0)) AS Check_,
SUM(IF(p.`PaymentMethod`='Credit Card',Amount,0)) AS Credit_Card_,
SUM( CASE PaymentMethod
WHEN 'Cash' THEN Amount
WHEN 'Check' THEN Amount
WHEN 'Credit Card' THEN Amount
END) AS Total
FROM
`payments` AS p
GROUP BY p.`ProductID`;
SQL FIDDLE: http://www.sqlfiddle.com/#!9/23d07d/18
SQL 小提琴：http://www.sqlfiddle.com/#!9 /23d07d /18
