如何在同一 select 语句中使用条件列值？

Question

我有类似的东西

（COMPLEX_EXPRESSION_N 代表一个长子查询）

select
  ID_Operation,
  FirstCheck = CASE WHEN (COMPLEX_EXPRESSION_1)= 0 then 0 else 1 end,
  SecondCheck = CASE WHEN (COMPLEX_EXPRESSION_2)= 0 then 0 else 1 end,
  ThirdCheck = CASE WHEN (COMPLEX_EXPRESSION_3)= 0 then 0 else 1 end,
  AllChecksOk = Case WHEN 
               (FirstCheck + SecondCheck + Third CHeck = 3) 
               Then 'OK' Else 'No' End
from 
  AllOperationsTable

是否可以像我在 AllChecksOk 行中那样使用 FirstCheck、SecondCheck、ThirdCheck ？

我不关心性能，这是每天在极少数记录上手动运行一次的东西，我只是想避免创建视图、表或临时表并将所有内容保留在单个 select 语句中。

作为替代方案，我可以这样做，但它使查询的可读性较差（因为我需要为每个复杂表达式编写两次）：

select
  ID_Operation,
  FirstCheck = CASE WHEN (COMPLEX_EXPRESSION_1)= 0 then 0 else 1 end,
  SecondCheck = CASE WHEN (COMPLEX_EXPRESSION_2)= 0 then 0 else 1 end,
  ThirdCheck = CASE WHEN (COMPLEX_EXPRESSION_3)= 0 then 0 else 1 end,
  AllChecksOk = Case WHEN 
               (COMPLEX_EXPRESSION_1+ COMPLEX_EXPRESSION_2+ 
               COMPLEX_EXPRESSION_3CHeck = 3) Then 'OK' Else 'No' End
from 
  AllOperationsTable

Answer 1

下面是一个派生表的解决方案

SELECT
  T.ID_Operation,
  FirstCheck   = CASE WHEN T.Expr1 = 0 THEN 0 ELSE 1 END,
  SecondCheck  = CASE WHEN T.Expr2 = 0 THEN 0 ELSE 1 END,
  ThirdCheck   = CASE WHEN T.Expr3 = 0 THEN 0 ELSE 1 END,
  AllChecksOk  = CASE WHEN T.Expr1 + T.Expr2 + T.Expr3 = 3 THEN 'OK' ELSE 'No' END
FROM
(
  SELECT
    ID_Operation,
    Expr1 = (COMPLEX_EXPRESSION_1),
    Expr2 = (COMPLEX_EXPRESSION_2),
    Expr3 = (COMPLEX_EXPRESSION_3)
  FROM 
    AllOperationsTable
) T

Answer 2

就我个人而言，我发现为此目的使用 CTE 或派生表有点令人困惑，因为您必须将事物嵌套一层并考虑嵌套含义。一种更简单的方法（至少在我看来）是使用 APPLY（或其他 RDBMS 中的标准 SQL LATERAL）来生成列表达式别名：

SELECT
  ID_Operation,
  FirstCheck,
  SecondCheck,
  ThirdCheck,
  AllChecksOk = CASE
    WHEN FirstCheck + SecondCheck + ThirdCheck = 3 THEN 'OK' ELSE 'NO'
  END
FROM
  AllOperationsTable
  CROSS APPLY (
    SELECT
      FirstCheck = CASE WHEN COMPLEX_EXPRESSION_1 = 0 THEN 0 ELSE 1 END,
      SecondCheck = CASE WHEN COMPLEX_EXPRESSION_1 = 0 THEN 0 ELSE 1 END,
      ThirdCheck = CASE WHEN COMPLEX_EXPRESSION_1 = 0 THEN 0 ELSE 1 END
  ) t

Answer 3

您无法在 select 中引用列别名，但可以使用 CTE，如下所示。

;WITH CTE AS
(
select
  ID_Operation,
  FirstCheck = CASE WHEN (COMPLEX_EXPRESSION_1)= 0 then 0 else 1 end,
  SecondCheck = CASE WHEN (COMPLEX_EXPRESSION_2)= 0 then 0 else 1 end,
  ThirdCheck = CASE WHEN (COMPLEX_EXPRESSION_3)= 0 then 0 else 1 end
from 
  AllOperationsTable
)
SELECT *,
       AllChecksOk = Case WHEN 
               (COMPLEX_EXPRESSION_1+ COMPLEX_EXPRESSION_2+ 
               COMPLEX_EXPRESSION_3CHeck = 3) Then 'OK' Else 'No' End
FROM CTE

您还可以使用CROSS APPLY定义3列别名，然后在主SELECT列表中引用它们如本例所示。