sql-server - SQL - CASE 语句中的别名

Question

我有这段代码(请看下面)。我不断收到错误消息:“无效的列名‘SuppFinish2’

SELECT

CASE
    WHEN [RegFinish] IS NULL THEN ''
    ELSE [RegFinish]
END AS [RegFinish],

CASE
    WHEN [SuppFinish] IS NULL THEN ''
    ELSE [SuppFinish]
END AS [SuppFinish2],

CASE
    WHEN [RegFinish]<[SuppFinish2] THEN '1'
    ELSE '0'
END AS [TEST]

FROM TABLE

是因为 [SuppFinish2] 是别名吗？谢谢!

Answer 1

正如您所说，由于别名和别名列只能在 order by 逻辑查询流程顺序上引用

with cte
as
(
SELECT
CASE
    WHEN [RegFinish] IS NULL THEN ''
    ELSE [RegFinish]
END AS [RegFinish],

CASE
    WHEN [SuppFinish] IS NULL THEN ''
    ELSE [SuppFinish]
END AS [SuppFinish2]
FROM TABLE
)
select 
CASE
    WHEN [RegFinish]<[SuppFinish2] THEN '1'
    ELSE '0'
END AS [TEST]
from cte