时间戳

首页 > 解决方案 > 两个字段的SQL总和

sql - 两个字段的SQL总和

问题描述

我有一个包含以下字段的表

vchnrno credit debit amount
JV1      BA10   0     100 
JV1      BA11   0      10 
JV1       0     BC10   90
JV1       0     BC11   20

通常这里的每个合资企业的贷方总和=借方总和

  1. 学分 = BA10 的总和 + BA11 = 110

  2. 借方 = BC10 之和 + BC 11 = 110

我想找到所有借方总和 - 贷方总和 > 0 的合资企业

标签： sqloracleoracle10g

解决方案

看起来SUM+CASE可能会完成这项工作。就是这样：

SQL> with test (vchnrno, credit, debit, amount) as
  2    (select 'JV1', 'BA10', '0'   , 100 from dual union all
  3     select 'JV1', 'BA11', '0'   ,  10 from dual union all
  4     select 'JV1', '0'   , 'BC10',  90 from dual union all
  5     select 'JV1', '0'   , 'BC11',  20 from dual union all
  6     --
  7     select 'xxx', 'XX20', '0'   ,  50 from dual union all
  8     select 'xxx', '0'   , 'xx30',  70 from dual
  9    )
 10  select vchnrno, sum_credit, sum_debit, sum_debit - sum_credit diff
 11  from (select vchnrno,
 12               sum(case when credit <> '0' then amount end) sum_credit,
 13               sum(case when debit  <> '0' then amount end) sum_debit
 14        from test
 15        group by vchnrno
 16       )
 17  where sum_debit - sum_credit > 0;

VCH SUM_CREDIT  SUM_DEBIT       DIFF
--- ---------- ---------- ----------
xxx         50         70         20

SQL>

我包含vchnrno = xxx在示例数据中，因为JV1不符合条件 ( sum of debit = sum of credit) 所以无论如何它都不会被返回。

推荐阅读