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

问题描述：

我有类似的东西

(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?

Is it possible to use FirstCheck, SecondCheck, ThirdCheck as I did in the AllChecksOk line?

我不关心性能，这是每天对少量记录手动执行一次的事情，我只是想避免创建视图，表或临时表，并将它们全部保留在单个select语句中.

I am not concerned about performance, this is something that is manually run once a day on a very small number of records, I just want to avoid to create views, tables or temporary tables and keep all in a single select statement.

作为一种替代方法，我可以做到这一点，但它会使查询的可读性降低(因为我需要为每个复杂表达式写两次):

As an altenrative I can do this, but it makes the query less readable (as I need to write twice every complex expression):

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

答

您不能在select中引用列别名，但可以使用如下所示的CTE.

You can't reference a column alias in the select but you can use a CTE as below.

;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列表中引用它们

You can also use CROSS APPLY to define the 3 column aliases then reference them in the main SELECT list as in this example.

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

相关推荐