工作(NOT IN)检索3行:
select DISTINCT d.* from Device d , Company c3
WHERE d.deviceid NOT IN
(
Select d1.deviceid from Device d1, Clone x1
WHERE d1.deviceid = x1.deviceID
AND
(
x1.XPath = 'hi'
OR x1.XPath = 'bye'
)
AND
(
EXISTS ( select * from (SELECT * FROM [dbo].[Split] ('T130SF0W2050', ',')) as s
WHERE x1.Value like '%' + s.items + '%' )
)
)
AND
d.companyid = c3.companyid and c3.companynumber in (SELECT * FROM [dbo].[Split] ('00223200', ','))
不工作(不存在):
select DISTINCT d.* from Device d , Company c3
WHERE NOT EXISTS
(Select * from Device d1, Clone x1
WHERE d1.deviceid = x1.deviceID
AND
(
x1.XPath = 'hi'
OR x1.XPath = 'bye'
)
AND
(
EXISTS ( select * from (SELECT * FROM [dbo].[Split] ('T130SF0W2050', ',')) as s
WHERE x1.Value like '%' + s.items + '%' )
)
)
AND
d.companyid = c3.companyid and c3.companynumber in (SELECT * FROM [dbo].[Split] ('00223200', ','))
我不确定我使用的现有语法是否正确,我应该从子查询中选择什么?我试过几种不同的组合。如果我放WHERE d.deviceid NOT EXISTS
,它就不会运行
解决方案(感谢Nikola):
在Exists子查询中添加AND d1.deviceid = d.deviceid
。
不同之处在于NOT IN查询返回的设备与公司匹配,但与内部查询规范不匹配。
要使NOT EXIST查询按写入方式运行(其中"按写入方式工作"指的是返回与顶部查询相同的结果),不可能存在任何与内部查询匹配的设备。如果任何设备与内部查询匹配,则查询不会返回任何结果。