sql - 总和列 Oracle

Question

您好，我在 3 个表中有数据，代码如下。

甲骨文

 select distinct 
 t2.coll_no,
 t1.account_number,
 t1.commitment_number,
 t3.APPRAISAL_AMT

 from table1 t1
 LEFT JOIN table2 t2
 ON T2.OBLIGATION_ACCOUNT_NUMBER = T1.ACCOUNT_NUMBER
 AND T2.OBLIGATION_NUMBER = T1.COMMITMENT_NUMBER
 LEFT jOIN table3 t3
 ON t3.COLL_NO = t3.cOLL_NO

每个 coll_no 都有不同的 afs_appraisal 数量。

而不是 5 行，唯一的区别是评估金额，我想要一行 5 个评估金额的总和。

标签： sqloraclemax

Answer 1

我想你只想要条件聚合：

select sum(case when t2.coll_no = <val1> then t3.appraisal_amt end) as appraisal_1,
       sum(case when t2.coll_no = <val2> then t3.appraisal_amt end) as appraisal_2,
       sum(case when t2.coll_no = <val3> then t3.appraisal_amt end) as appraisal_3,
       sum(case when t2.coll_no = <val4> then t3.appraisal_amt end) as appraisal_4,
       sum(case when t2.coll_no = <val5> then t3.appraisal_amt end) as appraisal_5
from table1 t1 left join
     table2 t2
     on T2.OBLIGATION_ACCOUNT_NUMBER = T1.ACCOUNT_NUMBER and
        T2.OBLIGATION_NUMBER = T1.COMMITMENT_NUMBER left join
     table3 t3
     on t3.COLL_NO = t3.cOLL_NO;