我有一个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中帮助实现这一点,这是非常感谢的。
发布于 2018-05-03 13:36:09
基于您提供的(更新的)数据,我认为这个查询(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发布于 2018-05-03 12:48:00
您应该仅将数据聚合到payment_id上。
SELECT
payment_id,
MAX(amount) amount,
(COUNT(DISTINCT student_id) - SUM(amount<0)) student_count,
SUM(amount) total_amount
FROM `transaction`
GROUP BY payment_id; https://stackoverflow.com/questions/50155024
复制相似问题