将类似的正负值行相加，在mysql中输出一个干净的结果

vuv7lop3 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(243)

我有一个 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中帮助实现这一点，我们将不胜感激。

mysql

来源：https://stackoverflow.com/questions/50155024/sum-up-similar-plus-and-minus-values-rows-to-output-a-clean-result-in-mysql

2条答案

按热度按时间

vohkndzv1#

您应该仅在付款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;

请参见SQLFiddle上的演示。

赞(0）回复(0）举报 2021-06-21

eeq64g8w2#

根据您提供的（更新的）数据，我认为这个查询（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 )，将查询的第三行更改为（updated sqlfiddle）

ABS(amount)*IF(SUM(amount)>0,1,-1) AS amount,

那会给你

4           -25     1               -25

赞(0）回复(0）举报 2021-06-21

我来回答

将类似的正负值行相加，在mysql中输出一个干净的结果

2条答案

相关问题

热门标签

最新问答