多例语句中的别名列名 [英] Alias Column name in mutiple case statement

问题描述

我正在尝试使用case语句检索别名列的值.

I am trying to retrieve a value for a alias column using case statement.

CASE 
    WHEN FieldA = 'TestA' THEN FieldA1
    WHEN FieldB = 'TestB' THEN FieldB1
    ELSE NULL
END AS Alias1 

但是当我尝试使用该别名来获取另一个别名的值时，出现错误

But when I try to use this alias to retrievce value for another alias , I am getting an error

CASE
    WHEN Alias1 = FieldA1 THEN FieldA0
    WHEN Alias1 = FieldB1 THEN FieldA1
    ELSE NULL
END AS Alias2 

我收到的错误消息是:

您能建议一种消除错误的方法或任何满足我要求的替代方法

Can you suggest a way to get rid of the error or any alternative approach which fulfills my requirements

推荐答案

您不能在同一SELECT子句中使用列别名.您有两种选择:

You can't use column aliases in the same SELECT clause. You have two choices:

使用子查询:

SELECT Alias1,
       CASE
            WHEN Alias1 = FieldA1 THEN FieldA0
            WHEN Alias1 = FieldB1 THEN FieldA1
            ELSE NULL
       END AS Alias2 
FROM (
    SELECT CASE 
                WHEN FieldA = 'TestA' THEN FieldA1
                WHEN FieldB = 'TestB' THEN FieldB1
                ELSE NULL
           END AS Alias1,
           FieldA1
           FieldB1
    ...)

，或者您可以重复在第一个CASE中使用的逻辑:

or you can repeat the logic that you used in the first CASE:

SELECT CASE 
            WHEN FieldA = 'TestA' THEN FieldA1
            WHEN FieldB = 'TestB' THEN FieldB1
            ELSE NULL
       END AS Alias1,
       CASE 
            WHEN FieldA = 'TestA' THEN FieldA0
            WHEN FieldB = 'TestB' THEN FieldB0
            ELSE NULL
       END AS Alias2

这篇关于多例语句中的别名列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！