sql - sql 嵌套“sum(case when) or”选择查询

Question

我有一个选择查询，我试图根据多个条件对一个值求和，按维度分组。输出将是大于 30、90 或 365 天的时间总和，基于类别，按类别分组。我无法破解此语法，希望您能提供帮助！

这个概念看起来像这样：

select
CATEGORY,
sum (case when 
        (CATEGORY = '1' and TIME >30) then sum (TIME - 30) or 
        (CATEGORY = '2' and TIME >90) then sum (TIME - 90) or
        (CATEGORY = '3' and TIME >365) then sum (TIME - 365) else 0 end) as output
from database.table
group by CATEGORY

提前致谢！

标签： sqlnestedcase

Answer 1

我怀疑你想要的语法是：

select CATEGORY,
       sum(case when CATEGORY = '1' and TIME > 30 then TIME - 30 
                when CATEGORY = '2' and TIME > 90 then TIME - 90
                when CATEGORY = '3' and TIME > 365 then TIME - 365
                else 0
           end) as output
from database.table
group by CATEGORY