sql - SQL:将案例表达式中的值用于下一列

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

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

谢谢参观！
-标记

最佳答案

查找表可以在这里提供帮助，但是您可以按原样使用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
;

关于sql - SQL:将案例表达式中的值用于下一列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/51371172/