mysql 基于两列计算债务和存款

Question

我有一个包含以下列的表格:

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  |
|---------|-------|

View on DB Fiddle

基于上述，如果我计算总债务为 0(总和(-100 + 50 + 50))。

但实际债务是 100 ( 50 + 50 ) 而存款是 100 ( -100 )。

我可以查询以根据我的数据输出下表吗？

 debt  deposit
 100     100

谢谢你。

债务 = 金钱必须如何支付。

存款=多少钱支付了更多。

Answer 1

您可以再使用一次聚合:

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<>fiddle demo