sql-server - SQL 服务器 : Case Statement Sum

Question

我有以下嵌套查询

select 
    sum(c.seats) as countPerfs, b.program, b.Prog_id
from        
    #LT_TEMP TableP
join        
    C_PROGRAM b on TableP.program_id = b.Prog_id
join        
    lt_data c on b.Program = c.program
join        
    LTR_BUDGET_REF a on TableP.program_id = a.ProgramID 
where       
    a.Participants = 'Y'
    and fyear = 2016
group by    
    b.Program, b.Prog_id

它返回如下所示的输出:

program_id  season  program performance workshops   artists_sess    participants    audience
6           2016    LU:     NULL       NULL         NULL            NULL            NULL
7           2016    NC:     NULL       NULL         NULL            NULL            NULL
11          2016    AC:     NULL       NULL         NULL            NULL            NULL
12          2016    PD:     NULL       NULL         NULL            NULL            NULL
19          2016    MC:     NULL       NULL         NULL            NULL            NULL

每个程序都有不同的逻辑——所以如果我想计算，可以说是参与者。但是当程序是 MC 时，我们只想返回/求和某些数据。但是当程序是LU时我们要计算所有的数据。等等。我如何构建该案例陈述。

例如，如果节目是 MC，我们希望对 c.seats(来自 lt_data)求和，但仅当价格 = 800 时。但是如果节目是 LU，我们希望对所有价格的座位求和。如果程序是 PD，我们想在价格为 800、500 和 300 的地方求和。诸如此类。它视具体情况而定，但我不确定如何构建它。

提前致谢。

这是完整的查询

UPDATE  
        #LT_TEMP 
SET     
        program = h.Program,
        participants =  h.countPerfs
FROM        
(
                select sum(c.seats) as countPerfs, b.program, b.Prog_id
                from        #LT_TEMP TableP
                join        C_PROGRAM b on TableP.program_id = b.Prog_id
                join        lt_data c on b.Program = c.program
                join        LTR_BUDGET_REF a on TableP.program_id = a.ProgramID 
                where       a.Participants = 'Y'
                and         fyear = 2016
                group by    b.Program, b.Prog_id
) h 
where h.Prog_id = #LT_TEMP.program_id
and h.Prog_id not in (27, 28, 29) 

Answer 1

我认为您试图在太高的级别上执行聚合和 CASE 逻辑。尝试在按程序分组的 lt_data 的子查询中执行此操作:

SELECT 
    c.Prog_id,
    fyear AS season,
    c.program, 
    SUM(l.seats) AS countPerfs
FROM        
    #LT_TEMP t JOIN        
    C_PROGRAM c ON 
    t.program_id = c.Prog_id JOIN        
    (SELECT
        fyear,
        program,
        SUM(CASE 
            WHEN program = 'mc:' AND price = 800 THEN seats 
            WHEN program = 'lu:' THEN seats
            WHEN program = 'pd:' and price in (300, 500, 800) then seats 
            ELSE seats
        END) AS calc
    FROM
        lt_data
    GROUP BY
        fyear, program) l ON 
    c.Program = l.program JOIN        
    LTR_BUDGET_REF b ON 
    TableP.program_id = b.ProgramID 
WHERE       
    b.Participants = 'Y'     AND 
    fyear = @fyear
GROUP BY  
    c.Prog_id,  
    fyear,
    c.Program

---

MS的definition中的相关语法示例是:搜索的 CASE 表达式:

CASE
     WHEN Boolean_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END