子查询参考新创建的列

Question

我一直在尝试基于SQL Server文档编写子查询。我正在尝试将SQL Server查询写入

1. 将varchar施加给int
2. 选择varchar为null的行选择行。

我的逻辑是：

1. 外部选择 从句获取所有行，而新列(q5int(为null
2. 创建执行铸件并创建新列(q5int(
的内部选择

我在做什么错？我会感谢我的逻辑有什么问题的解释，而不仅仅是如何修复我的代码。

select *  
from 
    (select 
         *, cast (NULLIF(q5,'NULL') as int) as q5int 
     from 
         ft_merge)
where
    q5int = NULL

Answer 1

您可以做到这一点，而不是子查询

select *  
 from ft_merge
 WHERE try_convert(int,q5) is null

Answer 2

让我们首先分析您的查询

               select *  from (
                select *,cast (NULLIF(q5,'NULL') as int) as q5int 
                from ft_merge
                ) WHERE q5int=NULL

1.nullif(q5，'null'(应为nullif(q5，null(，而不是引号

2. q5int = null应替换为" q5int is null"

创建测试数据

       Select * into #ft_merge
        from
        (
              Select 1 AS ID,'111' AS q5
              union
              Select 2, null
                  union
              Select 3,'2222'
                  union
              Select 4,null 
        )t

       /* using your query */  
       select *  from (
      select *,cast(q5 as int) as q5int 
        from #ft_merge where ISNUMERIC(q5) > 0 or q5 is null
      )t WHERE q5int is NULL
      /* another optimal way of doing this */
      select *,cast(q5 as int) as q5int 
      from #ft_merge where NULLIF(q5,NULL) is null