MySQL-如何将子查询中的列别名用于另一个子查询

Question

select name, 
(select sum(balance) from customers group by name having balance>0 and `type of contract`!="loan") as holdings, 
(select sum(balance) from customers group by name having balance<0 or `type of contract`="loan") as borrowings, 
(select case when holdings-borrowings>0 then "positive" else "negative" end from customers) as `positive/negative`, 
holdings-borrowings as total 
from customers 
group by name 
order by name;

错误代码：1054。"字段列表"中的未知列"holdings"。

表定义为，name varchar，type of contractvarchar，balance int。我知道错误在哪里，我不能使用子查询中的列别名，但我不知道如何用另一种方法执行查询。

Answer 1

您可以使用conditional aggregation尝试以下操作

注意：列别名不能用作投影列表中的引用，这就是您得到错误的原因

select name, 
sum(case when balance>0 and `type of contract`!="loan" then balance else 0 end) as holdings,
sum(case when balance<0 and `type of contract`="loan" then balance else 0 end) as borrowings,
case when (sum(case when balance>0 and `type of contract`!="loan" then balance else 0 end)-
sum(case when balance<0 and `type of contract`="loan" then balance else 0 end))>0 then 'positive' else 'negative' end as `positive/negative`,
sum(case when balance>0 and `type of contract`!="loan" then balance else 0 end)-
sum(case when balance<0 and `type of contract`="loan" then balance else 0 end) as total
from customers   
group by name 
order by name

或-

select name, holdings,borrowings,case when holdings-borrowings>0 then 'Postivie' else 'Negative' end as `positive/negative`,holdings-borrowings as total
from
(
select name, 
sum(case when balance>0 and `type of contract`!="loan" then balance else 0 end) as holdings,
sum(case when balance<0 and `type of contract`="loan" then balance else 0 end) as borrowings
from customers   
group by name
)A order by name