sql - 在选择子句的另一个案例中使用案例列

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。我怎样才能做到这一点？

标签： sqlfirebird

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;