我有一个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/

10-12 00:24
查看更多