我有一个 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中帮助实现这一点,我们将不胜感激。
2条答案
按热度按时间vohkndzv1#
您应该仅在付款id上聚合数据。
请参见SQLFiddle上的演示。
eeq64g8w2#
根据您提供的(更新的)数据,我认为这个查询(sqlfiddle)应该产生您想要的:
输出:
如果你想要
payment_id
4为阴性(匹配total_amount
),将查询的第三行更改为(updated sqlfiddle)那会给你