参考别名在SELECT列表

问题描述：

select FirstName, LastName, 
    Case 
    When LastName = 'Jones' 
    then 'N/A' 
    End as Other, 
    Case 
     When Other is not null 
     then 1 
    else 0 as Flag

的标志值依赖于其他，但作为其他是一个别名，标志字段不能识别其他。

我想，我可以在一个选择使用一个选择。有没有更好的方法让Flag识别Other别名列？

答

您不能引用别名选择和订购的外受，因为方式的查询分析。典型的解决方法是把它埋在派生表：

SELECT 
    FirstName, LastName, Other, 
    Flag = CASE WHEN Other IS NOT NULL THEN 1 ELSE 0 END 
FROM 
(
    SELECT FirstName, LastName, 
    CASE WHEN LastName = 'Jones' 
    THEN 'N/A' 
    END AS Other 
    FROM dbo.table_name 
) AS x;

geez我讨厌这个MSSQL。无用的贡献，我知道。 – youcantryreachingme 2017-07-31 23:11:41

@youcantryreachingme这实际上就是每个遵循标准的RDBMS。 – 2017-08-01 01:58:37

答

你将不得不使用SELECT在SELECT：

SELECT FirstName, LastName, Other, 
    Case When Other is not null 
     then 1 
     else 0 END as Flag 
FROM 
(
    select FirstName, LastName, 
     Case 
     When LastName = 'Jones' 
     then 'N/A' 
     End as Other 
    from yourTable 
) x

或评估的时候可以重复使用的Other场CASE声明Flag字段：

select FirstName, 
    LastName, 
    Case 
     When LastName = 'Jones' 
     then 'N/A' 
    End as Other, 
    case when LastName = 'Jones' -- your Other CASE code here 
     then 1 
     else 0 
    END as flag 
from yourTable

答

你也可以用公用表格表达n（CTE）：

;WITH cte AS 
(
SELECT 
    firstname, 
    lastname, 
    CASE WHEN lastname = 'Jones' THEN 'N/A' 
    END AS Other 
FROM @t 
) 
SELECT 
    firstname, 
    lastname, 
    CASE WHEN other IS NOT NULL THEN 1 
     ELSE 0 
    END AS Flag 
FROM cte

参考别名在SELECT列表

相关推荐