在 Where 子句或替代选项中使用别名? [英] Using Aliases in Where Clause or an Alternative Option?

问题描述

我如何让它工作，它在没有 Where 子句的情况下工作，否则使用 Where 子句，我会得到明显的错误，但这基本上是需要做的，有人知道如何解决这个问题吗?

How do I get this to work, it works without the Where Clause, otherwise with the Where clause, i get the obvious error, but that's basically what needs to be done, anyone know how to approach this?

select ID, 
       Name,
       case T.N 
         when 1 then City1
         when 2 then City2
         when 3 then City3
       end as City,
       case T.N 
         when 1 then State1
         when 2 then State2
         when 3 then State3
       end as State
from YourTable
  cross join (values(1),(2),(3)) as T(N)

    Where City is NOT Null

推荐答案

不能在 WHERE 子句中使用别名.要么重复表达式(混乱)，要么将 SELECT 放在子查询中，然后将 WHERE 子句放在外部查询中:

You can't use the alias in the WHERE clause. Either repeat the expression (messy) or else put your SELECT in a subquery and then put the WHERE clause in the outer query:

SELECT Id, Name, City, State
FROM
(
     SELECT
         ID, 
         Name,
         CASE T.N 
             WHEN 1 THEN City1
             WHEN 2 THEN City2
             WHEN 3 THEN City3
         END AS City,
         CASE T.N 
             WHEN 1 THEN State1
             WHEN 2 THEN State2
             WHEN 3 THEN State3
         END AS State
     FROM YourTable
     CROSS JOIN (VALUES(1),(2),(3)) AS T(N)
) T1
WHERE City IS NOT NULL

这篇关于在 Where 子句或替代选项中使用别名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！