CASE表达式访问列别名报错：无效列名'CurrentStatus'怎么解决

报错原因 #

SQL的逻辑执行顺序决定了同层级SELECT子句中定义的列别名，无法被同层级的其他表达式引用。数据库在解析SELECT语句时，会先处理FROM、WHERE、GROUP BY等子句，再处理SELECT中的计算逻辑，同一SELECT下的别名在被后续同层级表达式引用时还未完成绑定，因此会抛出列不存在的错误。

可行解决方案 #

方案1：重复编写别名对应的逻辑 #

最简单的处理方式，直接把CurrentStatus对应的CASE表达式复制到DateStartedStatus的判断逻辑中，无额外嵌套，性能不受影响，适合逻辑简短的场景：

SELECT p.[ID]
  ,p.[Name] as 'ProcurementName'
  ,p.[FundingDocumentNumber] as 'FundingDocumentNumber'
  ,p.[Status]
  ,p.[Comments] as 'Comments'
  ,p.[isSAVE]
  ,p.[InWorkDate]
  ,p.[RoutedDate]
  ,p.[FundsCertifiedDate]
  ,p.[AwardedDate]
  ,p.[TransactionType]
  ,p.[FNMSStatus]
  ,p.[Closed]
  ,p.[Archived]
  ,p.[Cancelled]
  ,(CASE
    WHEN p.[Status] = 'In Work' THEN 'Pending'
    ELSE p.[Status]
   END) as CurrentStatus
  ,(CASE
   WHEN (CASE WHEN p.[Status] = 'In Work' THEN 'Pending' ELSE p.[Status] END) = 'Awarded' THEN p.AwardedDate
   END) as DateStartedStatus
  ,(SELECT SUM(TotalCost) 
  FROM ProcurementsRequestLineItems subprlis
  LEFT JOIN RequestLineItems subrli ON subprlis.RequestLineItemID = subrli.ID
  WHERE ProcurementID = p.ID) as TotalCost
FROM Procurements p
WHERE p.Closed = 0 AND p.Archived = 0;

方案2：使用CTE（公用表表达式）封装内层逻辑 #

将包含CurrentStatus计算的查询逻辑封装到CTE中，外层查询就可以直接引用别名，逻辑分层清晰，适合复杂计算场景：

WITH ProcurementBase AS (
    SELECT p.[ID]
      ,p.[Name] as 'ProcurementName'
      ,p.[FundingDocumentNumber] as 'FundingDocumentNumber'
      ,p.[Status]
      ,p.[Comments] as 'Comments'
      ,p.[isSAVE]
      ,p.[InWorkDate]
      ,p.[RoutedDate]
      ,p.[FundsCertifiedDate]
      ,p.[AwardedDate]
      ,p.[TransactionType]
      ,p.[FNMSStatus]
      ,p.[Closed]
      ,p.[Archived]
      ,p.[Cancelled]
      ,(CASE
        WHEN p.[Status] = 'In Work' THEN 'Pending'
        ELSE p.[Status]
       END) as CurrentStatus
      ,(SELECT SUM(TotalCost) 
      FROM ProcurementsRequestLineItems subprlis
      LEFT JOIN RequestLineItems subrli ON subprlis.RequestLineItemID = subrli.ID
      WHERE ProcurementID = p.ID) as TotalCost
    FROM Procurements p
    WHERE p.Closed = 0 AND p.Archived = 0
)
SELECT *
    ,(CASE WHEN CurrentStatus = 'Awarded' THEN AwardedDate END) as DateStartedStatus
FROM ProcurementBase;

方案3：使用CROSS APPLY定义可复用别名（适用SQL Server、Oracle等支持APPLY的数据库） #

可以在FROM子句中通过CROSS APPLY提前计算CurrentStatus，后续所有同层级表达式都可以直接引用别名，不需要重复写逻辑也不需要多层嵌套：

SELECT p.[ID]
  ,p.[Name] as 'ProcurementName'
  ,p.[FundingDocumentNumber] as 'FundingDocumentNumber'
  ,p.[Status]
  ,p.[Comments] as 'Comments'
  ,p.[isSAVE]
  ,p.[InWorkDate]
  ,p.[RoutedDate]
  ,p.[FundsCertifiedDate]
  ,p.[AwardedDate]
  ,p.[TransactionType]
  ,p.[FNMSStatus]
  ,p.[Closed]
  ,p.[Archived]
  ,p.[Cancelled]
  ,cs.CurrentStatus
  ,(CASE WHEN cs.CurrentStatus = 'Awarded' THEN p.AwardedDate END) as DateStartedStatus
  ,(SELECT SUM(TotalCost) 
  FROM ProcurementsRequestLineItems subprlis
  LEFT JOIN RequestLineItems subrli ON subprlis.RequestLineItemID = subrli.ID
  WHERE ProcurementID = p.ID) as TotalCost
FROM Procurements p
CROSS APPLY (
    SELECT CASE WHEN p.[Status] = 'In Work' THEN 'Pending' ELSE p.[Status] END AS CurrentStatus
) cs
WHERE p.Closed = 0 AND p.Archived = 0;

内容的提问来源于stack exchange，提问作者klaurtar1