SQL中的连接AND行为异常 [英] Connected AND in SQL acts wierd
本文介绍了SQL中的连接AND行为异常的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
尝试在大型查询中查找问题，我已将导致意外结果的原因缩小为我不理解的行为.
阶段:
简易表:
Trying to find the problem in a large query I''ve narrowed the cause for unexpected results to a behaviour I do not comprehend.
Stage:
Simple table:
id voucherid orgid perid listtype
14 10 0 0 0
15 10 0 203986 0
16 10 151735 0 0
19 9 0 203986 1
20 9 151735 0 1
简单查询:
Simple Query:
DECLARE @per int
DECLARE @org int
SET @per = 203986
SET @org = 1517351
SELECT * FROM [voucher2contact]
WHERE
[perid] <> @per AND
[orgid] <> @org AND
listtype = 1
返回一行
Returns one line
id voucherid orgid perid listtype
20 9 151735 0 1
-编辑-
这是更正的版本:
-EDIT-
This is the corrected version:
-- listtype 0 ->Whitelisted
-- listtype 1 ->Blacklisted
-- Return not Blacklisted entries for @per + @org
的未列入黑名单的条目
DECLARE @per int
DECLARE @org int
SET @per = 203986
SET @org = 151735
SELECT *
FROM [ERA].[dbo].[voucher2contact]
WHERE
(
(
([perid] <> @per AND [perid] > 0)
)
OR
(
([orgid] <> @org AND [orgid] > 0)
)
)AND
listtype = 1
对自己的注意:编码超过14小时后，不要问愚蠢的问题；)
THX寻求帮助:D
Note to self : Do not ask stupid questions after more than 14hrs of coding ;)
THX for the help :D
推荐答案
您确定您在声明@org中没有丢失一位数字，它在int停在1位并有7位，而您在表格输入内容有6位数字.所以看起来SQL是正确的:)
Are you sure your not missing one digit in Declare @org, it stops at 1 in your int and has 7 digits, while your table entery has 6 digits. So it looks like SQL is correct :)
您的表包含orgid 151735和0.
但是在where子句中，您正在获取orgid<>>>>>>>> 1517351.的记录.
绝对是151735<> 1517351
我认为您必须更改变量@org中的值.
代替
SET @org = 1517351
使用
SET @ org = 151735.
我肯定会说SQL Server并不愚蠢.
your table contains orgid 151735 and 0.
But in where clause you are fetching records where orgid<>1517351.
Definitely 151735<>1517351
I think you have to change the value in variable @org.
Instead of
SET @org = 1517351
use
SET @org=151735.
I would say definitely SQL server is not stupid.
这篇关于SQL中的连接AND行为异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
