如何使用新列中列中的变量

Question

下面是一个示例:表“事务”中包含“代码、借方、信贷”

我试图执行以下查询：

Select code, 
       Var1 = sum(debit) ,
       Var2 = sum(credit),  
       Balance_debit = Var1 - Var2, 
       Balance_credit = Var2 - Var1 
from transactions 
group by code

我知道我能做到

Select code, 
       Var1 = sum(debit) ,
       Var2 = sum(credit),  
       Balance_debit =  sum(debit) -  sum(credit), 
       Balance_credit =   sum(credit) - sum(debit) 
from transactions 
group by code

但是后来我有了非常大的表达式，我想使用我从上一篇专栏中获得的变量。

我怎么才能解决这个问题？

Answer 1

你能做到的

With CTE as
(
Select code, 
       sum(debit) as d,
       sum(credit) as c,  
       Balance_debit = Var1 - Var2, 
       Balance_credit = Var2 - Var1 
from transactions 
group by code
)
select code
    ,d as Debilt
    ,c as Credit
    ,d-c as Balance_debit 
   ,c-d as Balance_credit 
 from CTE