T-SQL CASE语句依赖于同一SELECT查询中的另一个CASE语句 [英] T-SQL CASE statement relies on another CASE statement in same SELECT query

问题描述

我有一个SELECT查询，其中第二条CASE语句的结果可以取决于第一条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 
                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.

有什么想法吗?

推荐答案

SELECT子句的结果是并行计算的(就像正在对它们进行求值一样)，因此，一列的值不能依赖于另一列的值.解决方案是引入CTE或子查询，以便您具有多个SELECT子句:

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
    ...,
    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

这篇关于T-SQL CASE语句依赖于同一SELECT查询中的另一个CASE语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！