sql - CASE WHEN 语句中需要的 SQL 别名列名

Question

如何引用别名“新日期”？我不断收到“无效的列名'NewDate'”错误。

CASE
    WHEN p.Program IN ('program1') THEN (date_needed + 10)
    WHEN p.Program IN ('program2') THEN (date_needed + 20)
END AS [NewDate]

CASE 
    WHEN date_needed < [NewDate] THEN 'Program On Time' 
    ELSE 'Program Late'
END AS [Modified New Date]

标签： sqlsql-serversql-server-2014

Answer 1

由于逻辑查询处理顺序，不可能引用NewDate同一块中的列。为此，您可以使用公用表表达式，如下所示：

WITH Base_CTE
AS
(
SELECT CASE
    WHEN p.Program IN ('program1') THEN (date_needed + 10)
    WHEN p.Program IN ('program2') THEN (date_needed + 20)
END AS [NewDate], date_nedded
FROM tablename
)
SELECT CASE 
    WHEN date_needed < [NewDate] THEN 'Program On Time' 
    ELSE 'Program Late'
END AS [Modified New Date]
FROM Base_CTE;