sql-server-2016 - SQL：不能存在有“或”吗？

Question

我有一个不存在的 SQL 查询，用于从最终结果中排除一组值。

select distinct 
from [rpt_StockInventorySummary] a
where a.[DepartmentId] ='P'
and not exists (
select *
from rpt_StockInventorySummary b
where b.DepartmentId = 'p' 
and b.Manufacturer = 'warrington'
and b.LowestGroup = 57 and b.Instock = 0
and b.Barcode = a.Barcode
)
order by a.SortOrder

查询工作得很好，但现在我需要修改 SQL，以便从最终结果中排除另一组值。所以我尝试像这样修改 NOT EXISTS 中的 SQL。

select *
from rpt_StockInventorySummary b
where b.DepartmentId = 'p' 
and b.Manufacturer = 'warrington'
and (b.LowestGroup = 57 and b.Instock = 0)
or b.LowestGroup = 60
and b.Barcode = a.Barcode

查询本身运行并返回值就好了。但是当我运行整个查询时，我没有得到任何结果。我怎样才能解决这个问题？

标签： sql-server-2016not-exists

Answer 1

SQL 具有明确定义的操作顺序。AND 子句在 OR 子句之前计算。因此，您的新查询将选择满足以下条件的记录：

b.DepartmentId = 'p' 
and b.Manufacturer = 'warrington'
and (b.LowestGroup = 57 and b.Instock = 0)

或者

b.LowestGroup = 60
and b.Barcode = a.Barcode

我怀疑以下内容将为您提供所需的内容：

select *
from rpt_StockInventorySummary b
where b.DepartmentId = 'p' 
and b.Manufacturer = 'warrington'
and (b.LowestGroup = 57 and b.Instock = 0 or b.LowestGroup = 60)
and b.Barcode = a.Barcode