sql - 连接来自同一个表的两个聚合查询 - SQL Server

Question

我有两个查询，它们都是从同一个表中聚合的。我不确定是否必须将这两个查询连接在一起，或者是否可以使用一个 select 语句来完成。目标是输出一个汇总每个学生的总费用和总退款的表格。

查询 #1：

select 
    s.learners_id, sum(charge.total_amount) charge_amount
from 
    fact_student_transactions_t charge 
inner join 
    dim_students_t s on s.students_sk_id = charge.students_sk_id
left join 
    object_statuses_t os on os.object_statusid = charge.transaction_status_id
where 
    os.status_name = 'Success' 
    and charge.tran_type = 'CHARGE' 
    and charge.curr_in = 1
group by 
    s.learners_id

查询 #2：

select 
    s.learners_id, sum(refund.total_amount) refund_amount
from 
    fact_student_transactions_t refund
inner join 
    dim_students_t s on s.students_sk_id = refund.students_sk_id
left join 
    object_statuses_t os on os.object_statusid = refund.transaction_status_id
where 
    os.status_name = 'Success' 
    and refund.tran_type = 'Refund' 
    and refund.trans_description not in ('Amount Successfully Transfered to Prepaid Balance.', 'Amount Successfully Transfered.') 
    and refund.payment_method != 'Transfer' 
    and refund.curr_in = 1
group by 
    s.learners_id

标签： sqlsql-serverjoinaggregate-functions

Answer 1

您可以使用条件聚合。此外，由于where子句的性质，left join是不必要的 - 无论如何都会过滤掉不匹配的记录。

所以：

select s.learners_id,
       sum(case when st.tran_type = 'CHARGE' then st.total_amount else 0 end) as charge_amount,
       sum(case when st.tran_type = 'Refund' and 
                     st.trans_description not in ('Amount Successfully Transfered to Prepaid Balance.', 'Amount Successfully Transfered.') 
and
                     st.payment_method <> 'Transfer'
                then st.total_amount else 0 
           end) as refund_amount
from fact_student_transactions_t st join
     dim_students_t s 
     on s.students_sk_id = t.students_sk_id join
     object_statuses_t os 
     on os.object_statusid = t.transaction_status_id
where os.status_name = 'Success' and 
      st.curr_in = 1 and
      st.tran_type in ('CHARGE', 'Refund')
group by s.learners_id