gpt4 book ai didi

mysql - 如何根据mysql中的user_id对列本身的值进行求和和减去

转载 作者:行者123 更新时间:2023-11-29 10:07:05 25 4
gpt4 key购买 nike

我有一个名为 payment 的表。我只想计算每个用户的总付款额。它有payer_user_id , payee_user_id , amount , ... .

--------------------------------------------------
| ID | payer_user_id | payee_user_id | amount | ..
--------------------------------------------------
| 1 | 100 | 1 | 1000 |
--------------------------------------------------
| 2 | 200 | 1 | 20000 |
------------------------------------------------
| 3 | 1 | 100 | 50

例如,user_id = 100,total_ payment = 950,因为他/她向我支付的金额为1000然后我向他/她支付了金额50 。所以支付总额为950 。如何运行查询来获取结果?

(SELECT sum(amount) FROM payment  GROUP BY BYPAYER_USER_ID) -
(SELECT sum(amount) FROM payment GROUP BY PAYEE_USER_ID) total_debpt

ORDER BY total_debpt
LIMIT 10

最佳答案

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,payer_user_id INT NOT NULL
,payee_user_id INT NOT NULL
,amount INT NOT NULL
);

INSERT INTO my_table VALUES
(1 ,100 ,1 ,1000 ),
(2 ,200 ,1 ,20000 ),
(3 ,1 ,100 ,50 );


SELECT user_id
, SUM(amount) total
FROM
(
SELECT payer_user_id user_id, amount FROM my_table
UNION ALL
SELECT payee_user_id, amount*-1 FROM my_table
) x
GROUP
BY user_id;
+---------+--------+
| user_id | total |
+---------+--------+
| 1 | -20950 |
| 100 | 950 |
| 200 | 20000 |
+---------+--------+

关于mysql - 如何根据mysql中的user_id对列本身的值进行求和和减去,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/51743572/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com