在选择子句中的另一个大小写中使用大小写列

Question

我有一个带有case语句的 select 子句，我需要创建另一个case语句来比较前一个case语句创建的列。像这样：

select client
,discount
,(case when sales_avg>10000 then 30 
when sales_avg>5000 then 20 
else 0 end) discount_rule
,(case when discount < discount_rule then 1 else 0 end) status 
from sales;

我收到一条消息，discount_rule is unknown.我怎样才能做到这一点？

Answer 1

您可以使用公用表表达式(CTE( 并将 CTE 中的 CTE 引用为：

with CTE_discount_rule as
(
select client,
discount,
(case when sales_avg>10000 then 30
when sales_avg>5000 then 20 
else 0 end) as discount_rule
from sales
),
CTE_Final_Status as
(
select client,
discount,
discount_rule,
(case when discount < discount_rule then 1 else 0 end) as status 
from CTE_discount_rule
)
select * from CTE_Final_Status;

Answer 2

最简单的方法是使用返回列discount_rule的子查询：

select t.client, t.discount, t.discount_rule,
case 
when discount < discount_rule then 1 
else 0 
end status
from (
select client, discount,
case 
when sales_avg > 10000 then 30 
when sales_avg > 5000 then 20 
else 0 
end discount_rule
from sales
) t