未在操作员中运行时未找到记录 [英] No records found when running not in operator

问题描述

我试图从一张表中获取记录，但不包括某些记录(Union 中的订单号).谁能告诉我这个查询有什么问题.运行后我没有得到任何记录.

I am trying to get records from one table excluding some records (Order No.'s in the Union). Can anybody tell me what could be wrong with this query. I am getting no records after running it.

SELECT * 
FROM [dbo].[FMD15_18] 
WHERE [OrderNo] NOT IN  ((SELECT OrderNo 
                          FROM [dbo].[FMD15_18] 
                          WHERE [Item Description] Like '%AP%')
                          UNION ALL 
                          SELECT [OrderNo] FROM [dbo].[AP&C]
                         )

推荐答案

我会用 NOT EXISTS 代替:

SELECT t.*
FROM [dbo].[FMD15_18] t
WHERE NOT EXISTS (SELECT 1 
                  FROM [dbo].[FMD15_18] t1 
                  WHERE t1.OrderNo = t.OrderNo AND 
                        t1.[Item Description] Like '%AP%') AND
      NOT EXISTS (SELECT 1 
                  FROM [dbo].[AP&C] a
                  WHERE a.OrderNo = t.OrderNo);

但是，我怀疑当前查询存在一些 nullable 问题.如果是这样，那么您需要在 subquery 中使用 IS NOT NULL 进行调整.

However, i suspect some nullable issues with current query. If so, then you need to fiter out with IS NOT NULL in subquery.

这篇关于未在操作员中运行时未找到记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！