T-SQL CASE语句依赖于同一SELECT查询中的另一个CASE语句

I have a SELECT query where the result of the second CASE statement can depend on the result of the first CASE statement - something like:

我有一个SELECT查询,其中第二个CASE语句的结果可以取决于第一个CASE语句的结果 - 类似于:

SELECT      ..., 
            CASE 
                WHEN dbo.Table1.Description LIKE '%car%' THEN 'Car'
                WHEN ... 
                ELSE 'Unclassified'
            END AS Product, 
            CASE 
                WHEN dbo.Table2.Description LIKE '%my%brand%' THEN 'Branded'
                WHEN Product='Unclassified' THEN 'Unclassified'
                ELSE 'Generic'
            END AS Brand,
            ...
FROM        ...

Where Brand is 'Unclassified' if the query can't find the brand name in a description column and the Product column has also been determined to have value 'Unclassified'. At the moment this statement only ever outputs 'Branded' or 'Generic' Brand types. Even when Product is 'Unclassified' it still gives 'Generic' which is not the output I need.

如果查询无法在描述列中找到品牌名称且“产品”列也已确定具有“未分类”值,则“品牌”为“未分类”。目前,此声明仅输出“品牌”或“通用”品牌类型。即使Product是'Unclassified',它仍然会给出'Generic',这不是我需要的输出。

Any ideas?

1 个解决方案

#1

The results of a SELECT clause are computed (as if they're being evaluated) in parallel - as such, one column's value cannot depend on another one's. The solution is to introduce a CTE or subquery so that you have multiple SELECT clauses:

SELECT子句的结果是并行计算的(就好像它们被评估一样) - 因此,一列的值不能依赖于另一列的值。解决方案是引入CTE或子查询,以便您有多个SELECT子句:

SELECT
    ...,
    CASE 
        WHEN t.T2Description LIKE '%my%brand%' THEN 'Branded'
        WHEN Product='Unclassified' THEN 'Unclassified'
        ELSE 'Generic'
    END AS Brand
FROM (
    SELECT      ..., 
        CASE 
            WHEN dbo.Table1.Description LIKE '%car%' THEN 'Car'
            WHEN ... 
            ELSE 'Unclassified'
        END AS Product, 
        dbo.Table2.Description as T2Description,
        ...
    FROM        ...
) t