SQL Server中如何过滤SELECT子句IIF生成的别名计算列

根本原因 #

SQL的逻辑执行顺序决定了WHERE子句的执行优先级高于SELECT子句：

• 数据库执行查询时会先执行FROM/JOIN、WHERE、GROUP BY、HAVING这些阶段，最后才执行SELECT阶段的别名定义、字段计算、排序规则处理
• 你在SELECT中用IIF生成的Estado别名，在WHERE执行阶段还未被解析生成，数据库只会从关联的基础表中查找Estado字段，自然会报字段不存在的错误

常用解决方法 #

方法1：WHERE子句直接复用判断逻辑（最简便高效） #

直接把SELECT中Estado的计算逻辑放到WHERE子句中即可，还可以进一步简化逻辑避免冗余判断：

SELECT 
        EM.Id
        , C.Descripcion AS Clase
        , T.IdClaseEM
        , T.Descripcion AS Tipo
        , EM.IdTipoEM
        , EM.IdCentroMedico
        , CM.Descripcion AS CentroMedico
        , EM.FechaEvaluacion
        , EM.IdEmpleado
        , P.Nombres + P.ApellidoPaterno + P.ApellidoMaterno AS Persona
        , EM.Aptitud
        , IIF(EM.FechaCaducidad > GETDATE(), 'Vencido' , 'Vigente') AS Estado 
        , COUNT(*) OVER() TotalRecords
    FROM
        EvaluacionMedica AS EM
        INNER JOIN TipoEM AS T ON EM.IdTipoEM = T.Id            
        INNER JOIN ClaseEM AS C ON T.IdClaseEM = C.Id
        INNER JOIN Empleado AS E ON EM.IdEmpleado =  E.Id
        INNER JOIN Persona AS P ON E.IdPersona = P.Id
        LEFT JOIN CentroMedico AS CM ON EM.IdCentroMedico = CM.Id
    WHERE
        -- 直接复用计算逻辑，也可以简化为 EM.FechaCaducidad > GETDATE()，效率更高
        IIF(EM.FechaCaducidad > GETDATE(), 'Vencido' , 'Vigente') = 'Vencido'

如果只是要筛选过期的记录，直接写EM.FechaCaducidad > GETDATE()即可，不需要走IIF判断，性能更好。

方法2：用CTE（公用表表达式）嵌套后筛选 #

如果计算逻辑复杂不想重复写，可以用CTE把原查询包一层，外层再引用别名筛选：

WITH EvaluacionMedicaResult AS (
    SELECT 
            EM.Id
            , C.Descripcion AS Clase
            , T.IdClaseEM
            , T.Descripcion AS Tipo
            , EM.IdTipoEM
            , EM.IdCentroMedico
            , CM.Descripcion AS CentroMedico
            , EM.FechaEvaluacion
            , EM.IdEmpleado
            , P.Nombres + P.ApellidoPaterno + P.ApellidoMaterno AS Persona
            , EM.Aptitud
            , IIF(EM.FechaCaducidad > GETDATE(), 'Vencido' , 'Vigente') AS Estado 
            , COUNT(*) OVER() TotalRecords
        FROM
            EvaluacionMedica AS EM
            INNER JOIN TipoEM AS T ON EM.IdTipoEM = T.Id            
            INNER JOIN ClaseEM AS C ON T.IdClaseEM = C.Id
            INNER JOIN Empleado AS E ON EM.IdEmpleado =  E.Id
            INNER JOIN Persona AS P ON E.IdPersona = P.Id
            LEFT JOIN CentroMedico AS CM ON EM.IdCentroMedico = CM.Id
)
SELECT * FROM EvaluacionMedicaResult WHERE Estado = 'Vencido'

注意：这种写法下TotalRecords统计的是CTE中全量数据的总条数，不是筛选后的条数，如果需要统计筛选后的总条数，直接用方法1即可。

方法3：用子查询嵌套后筛选 #

和CTE逻辑一致，只是用派生表实现：

SELECT * FROM (
    SELECT 
            EM.Id
            , C.Descripcion AS Clase
            , T.IdClaseEM
            , T.Descripcion AS Tipo
            , EM.IdTipoEM
            , EM.IdCentroMedico
            , CM.Descripcion AS CentroMedico
            , EM.FechaEvaluacion
            , EM.IdEmpleado
            , P.Nombres + P.ApellidoPaterno + P.ApellidoMaterno AS Persona
            , EM.Aptitud
            , IIF(EM.FechaCaducidad > GETDATE(), 'Vencido' , 'Vigente') AS Estado 
            , COUNT(*) OVER() TotalRecords
        FROM
            EvaluacionMedica AS EM
            INNER JOIN TipoEM AS T ON EM.IdTipoEM = T.Id            
            INNER JOIN ClaseEM AS C ON T.IdClaseEM = C.Id
            INNER JOIN Empleado AS E ON EM.IdEmpleado =  E.Id
            INNER JOIN Persona AS P ON E.IdPersona = P.Id
            LEFT JOIN CentroMedico AS CM ON EM.IdCentroMedico = CM.Id
) AS t
WHERE t.Estado = 'Vencido'

内容的提问来源于stack exchange，提问作者Sergio Morera Sanchez