SQL:对下一列使用 case 表达式中的值

Question

试图想出一种更有效的方式来编写我的查询。我有一个汇总会计 GL 的长案例陈述，我的目标是在三列中汇总。

Select 
    Month, Sum(Value1), Sum (Budget),
    Case 
       When Field1 In (1001, 1002, 1003, 1004) Then 'Hardware Expense'
       When Field1 In (2001, 2002, 2003, 2004) Then 'Software Expense'
       When Field1 In (3001, 3002, 3003, 3004) Then 'Product Sales' 
    End As "Aggregation1",
    Case 
       When Field1 In (1001, 1002, 1003, 1004, 2001, 2002, 2003) Then 'Expenses'
       When Field1 In (3001, 3002, 3003, 3004) Then 'Sales' End As "Aggregation2"

虽然这行得通，但我的实际陈述要长得多。理想情况下，我会将“硬件费用”和“软件费用”称为变量。

When Aggregation1 In ('Hardware Expense', 'Software Expense') then 'Expenses'

最终输出为

Month, Sum(Value1), Sum (Budget), Aggregation1, Aggregation2, Aggregation3

感谢您的关注!-马克

Answer 1

查找表在这里可以提供帮助，但您可以按原样使用 CROSS APPLY 运算符动态创建命名列，您可以在同一查询中引用它们。

请注意，稍后在查询中如何使用新定义的 Aggregation1 列。

SELECT
    Month, Sum(Value1), Sum (Budget),
    A1.Aggregation1,
    A2.Aggregation2
FROM
    SomeTable
    CROSS APPLY
    (
        SELECT
            CASE
                WHEN Field1 In (1001, 1002, 1003, 1004) Then 'Hardware Expense'
                WHEN Field1 In (2001, 2002, 2003, 2004) Then 'Software Expense'
                WHEN Field1 In (3001, 3002, 3003, 3004) Then 'Product Sales' 
            END AS Aggregation1
    ) AS A1
    CROSS APPLY
    (
        SELECT
            CASE
                WHEN Aggregation1 In ('Hardware Expense', 'Software Expense') then 'Expenses'
                WHEN Aggregation1 In ('Product Sales') then 'Sales'
            END AS Aggregation2
    ) AS A2
;