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

jexiocij 于 2022-11-21 发布在其他

关注(0)|答案(2)|浏览(142)

我有一种感觉
（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
  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

sql-server

来源：https://stackoverflow.com/questions/5790697/how-to-use-conditional-columns-values-in-the-same-select-statement

2条答案

按热度按时间

m1m5dgzv1#

您不能引用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列表as in this example中引用它们。

赞(0）回复(0）举报 2022-11-21

3vpjnl9f2#

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

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

赞(0）回复(0）举报 2022-11-21

我来回答

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

2条答案

相关问题

热门标签

最新问答