我有一个包含以下列的表:
CREATE TABLE tblapp (
`app_id` INTEGER,
`cust_id` INTEGER,
`app_price` INTEGER,
`app_price_paid` INTEGER
);
INSERT INTO tblapp
(`app_id`, `cust_id`, `app_price`, `app_price_paid`)
VALUES
('1', '1', '100', '100'),
('2', '2', '50', '0'),
('3', '1', '0', '100'),
('4', '3', '100', '50');
我有以下sql和输出:
SELECT
cust_id,
(sum(COALESCE(app_price,0)) - sum(COALESCE(app_price_paid,0))) as total
FROM tblapp
group by cust_id;
| cust_id | total |
| ------- | ----- |
| 1 | -100 |
| 2 | 50 |
| 3 | 50 |
|---------|-------|
DB Fiddle 视图
基于以上情况,如果我计算总债务为0(总额(-100+50+50(之和(。
但实际债务是100(50+50(,存款是100(-100(。
我可以根据我的数据进行查询以输出下表吗?
debt deposit
100 100
谢谢。
债务=必须如何付款。
押金=多交了多少钱。
您可以再次使用聚合:
WITH cte AS (
SELECT cust_id,
(sum(COALESCE(app_price,0)) - sum(COALESCE(app_price_paid,0))) as total
FROM tblapp
group by cust_id
)
SELECT SUM(CASE WHEN total< 0 THEN -total END) AS debt,
SUM(CASE WHEN total>= 0 THEN total END) AS deposit
FROM cte;
db<>小提琴演示