我有一个transaction
表,我需要在其中生成摘要。
+---------------+------------+------------+--------+--------+
|transaction_id | student_id | payment_id | amount | action |
+---------------+------------+------------+--------+--------+
| 1 | 1 | 1| 100 | 1 |
| 2 | 1 | 2| 50 | 1 |
| 3 | 1 | 3| 0 | 1 |
| 4 | 1 | 4| 25 | 1 |
| 5 | 1 | 5| 200 | 1 |
| 6 | 2 | 1| 100 | 1 |
| 7 | 2 | 2| 50 | 1 |
| 8 | 2 | 2| -50 | 0 |
| 9 | 2 | 4| -25 | 0 |
| 10 | 2 | 5| -200 | 0 |
| 11 | 3 | 2| 75 | 1 |
| 12 | 3 | 4| -25 | 0 |
+---------------+------------+------------+--------+--------+
(操作:1 =付款,0 =取消)
我可以通过以下查询获取交易摘要。
SELECT payment_id, amount, COUNT(amount) AS student_count, SUM(amount) AS total_amount
FROM transaction t
GROUP BY payment_id, amount;
结果是。
+------------+--------+---------------+--------------+
| payment_id | amount | student_count | total_amount |
+------------+--------+---------------+--------------+
| 1 | 100 | 2 | 200 |
| 2 | -50 | 1 | -50 |
| 2 | 50 | 2 | 100 |
| 2 | 75 | 1 | 75 |
| 3 | 0 | 1 | 0 |
| 4 | -25 | 2 | -50 |
| 4 | 25 | 1 | 25 |
| 5 | 200 | 1 | 200 |
| 5 | -200 | 1 | -200 |
+------------+--------+---------------+--------------+
但是,总而言之,我想对每个相似的数量进行加和减,以保持其清洁度。因此,需求是如下所示的输出,将正负值相加以显示结果,如果它们的总和为零,则不需要显示该行。
+------------+--------+---------------+--------------+
| payment_id | amount | student_count | total_amount |
+------------+--------+---------------+--------------+
| 1 | 100 | 2 | 200 |
| 2 | 50 | 1 | 50 |
| 2 | 75 | 1 | 75 |
| 3 | 0 | 1 | 0 |
| 4 | -25 | 1 | -25 |
+------------+--------+---------------+--------------+
如果有人可以在MySQL中帮助实现这一目标,我们将不胜感激。
最佳答案
根据您提供的(更新的)数据,我认为此查询(SQLFiddle)应该产生您想要的内容:
SELECT
payment_id,
ABS(amount) AS amount,
ABS(SUM(IF(action=1,1,-1))) AS student_count,
SUM(amount) AS total_amount
FROM transaction
GROUP BY payment_id, ABS(amount)
HAVING total_amount != 0 OR amount = 0
输出:
payment_id amount student_count total_amount
1 100 2 200
2 50 1 50
2 75 1 75
3 0 1 0
4 25 1 -25
如果希望
payment_id
4的数量为负(与total_amount
匹配),请将查询的第三行更改为(更新的SQLFiddle) ABS(amount)*IF(SUM(amount)>0,1,-1) AS amount,
那会给
4 -25 1 -25
关于mysql - 汇总相似的正负值行以在MySQL中输出干净的结果,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/50155024/