对输出没有影响的Case语句

Question

我正在尝试获得基于银行审批状态的捐款总额，并按礼物种类分组。然而，脚本在不同的行上输出RR和NR捐赠(参见下面的脚本)。看起来case语句根本不起作用。

select gift_kind,
    case
        when c.bank_approval_status = 'AP' then 
            sum(c.charge_amount) end approved,  
    case    
        when c.bank_approval_status in ('RR','NR') then 
            sum(c.charge_amount) end rejected,
    case    
        when c.bank_approval_status = 'AR' then 
            sum(c.charge_amount)*-1 end refunded,
    case 
        when c.bank_approval_status not in ('AR','AP','RR','NR')  then 
            sum(c.charge_amount) end other_status
from charge_log c, transactions t
where c.account_id=t.account_id
and c.process_id= 'CHG - 02532'
and c.gift_date=t.gift_date
and c.gift_seq=t.gift_seq
and C.PLEDGE_NUMBER=t.pledge_number
and t.sts='A'
group by  t.fund_type, t.gift_kind, c.bank_approval_status
order by gift_kind asc

Answer 1

我相信你正在寻找这样的逻辑：

select gift_kind,
       sum(case when c.bank_approval_status = 'AP' then c.charge_amount 
           end) as approved,  
       sum(case when c.bank_approval_status in ('RR', 'NR') then c.charge_amount
           end) as rejected,
       sum(case when c.bank_approval_status in ('AR') then c.charge_amount*-1
           end) as refunded,
       sum(case when c.bank_approval_status not in ('AR','AP','RR','NR') then c.charge_amount
           end) other_status
from charge_log c join
     transactions t
     on c.account_id = t.account_id and
        c.gift_date = t.gift_date and
        c.gift_seq = t.gift_seq and
        C.PLEDGE_NUMBER = t.pledge_number
where c.process_id = 'CHG - 02532' and t.sts = 'A'
group by t.fund_type, t.gift_kind
order by gift_kind asc

备注：

• 学习使用正确的JOIN语法。切勿在FROM子句中使用逗号。
• JOIN条件应全部在ON子句中，而不是WHERE子句中。来自GROUP BY.
• I的
• CASE是bank_approval_status的参数，它不知道fund_type为什么在GROUP BY中。你可能是有原因的，所以我留下了它。

Answer 2

您需要将case子句放在sums中，并添加else 0以确保不会因此而获得null：

sum(case
    when c.bank_approval_status = 'AP' then 
        c.charge_amount else 0 end) approved,  
sum(case    
    when c.bank_approval_status in ('RR','NR') then 
        c.charge_amount else 0 end) rejected,
sum(case    
    when c.bank_approval_status = 'AR' then 
        -c.charge_amount else 0 end) refunded,
sum(case 
    when c.bank_approval_status not in ('AR','AP','RR','NR')  then 
        c.charge_amount else 0 end) other_status

而且您的group by应该很少出现在聚合中使用的列上(这里是sum中的bank_approval_status )。更改为：

group by  gift_kind

根据您的方案，可能需要将其他字段添加到group by子句中，但是也可以将它们放入select和order by子句中。