当前位置: 代码迷 >> Sql Server >> 疑难杂症:and跟or
  详细解决方案

疑难杂症:and跟or

热度:227   发布时间:2016-04-24 19:44:35.0
疑难杂症:and和or
问题:一张表tj_lmj_sp_1有如下字段:(spid varchar(255),cpid varchar(30),acct_item_type_id decimal(9,0),acct_item_type_name varchar(200),charge decimal(16,5))
现要求:排除所有spid为0或为null的记录,并且排除所有cpid为0或为null的记录,然后求sum(charge)。

根据此要求,写出的sql如下:
sql1:
select sum(charge) from 
(select * 
 from (select * from tj_lmj_sp_1 where cpid is not null and cpid <> 0) a
 where spid is not null and spid <> 0
) b

另外换种写法,结果正确:
sql2:
select spid,cpid,acct_item_type_id,acct_item_type_name,sum(charge)
from tj_lmj_sp_1
where (spid is not null and spid <> 0)  --排除所有spid为0或为null的
  and (cpid is not null and cpid <> 0)  --排除所有cpid为0或为null的
group by  spid,cpid,acct_item_type_id,acct_item_type_name

再换种写法,结果错误:
sql3:
select spid,cpid,acct_item_type_id,acct_item_type_name,sum(charge)
from tj_lmj_sp_1
where (spid is not null or spid <> 0)  --排除所有spid为0或为null的
  and (cpid is not null or cpid <> 0)  --排除所有cpid为0或为null的
group by  spid,cpid,acct_item_type_id,acct_item_type_name

sql3统计的结果中还包含有sqid=0或者是cpid=0的记录,sql3错在哪里呢?
从逻辑上看,感觉sql3是正确的,sql2是错误的,对sql2做如下变形:
select count(*) from tj_lmj_sp_1 where spid is not null and spid <> 0
select count(*) from tj_lmj_sp_1 where cpid is not null and cpid <> 0
这两句sql统计的结果一定是0,但sql2的最终结果就是正确的,sql3的结果就是错误的,这是为何?
如果把sql3 where条件里面的and换成or也是错误的,即:
select spid,cpid,acct_item_type_id,acct_item_type_name,sum(charge)
from tj_lmj_sp_1
where (spid is not null or spid <> 0)  --排除所有spid为0或为null的
   or (cpid is not null or cpid <> 0)  --排除所有cpid为0或为null的
group by  spid,cpid,acct_item_type_id,acct_item_type_name

------解决方案--------------------
引用:
现要求:排除所有spid为0或为null的记录,并且排除所有cpid为0或为null的记录,然后求sum(charge)。

根据此要求,写出的sql如下:

另外换种写法,结果正确:
sql2:
select spid,cpid,acct_item_type_id,acct_item_type_name,sum(charge)
from tj_lmj_sp_1
where (spid is not null and spid <> 0)  --排除所有spid为0或为null的
  and (cpid is not null and cpid <> 0)  --排除所有cpid为0或为null的
group by  spid,cpid,acct_item_type_id,acct_item_type_name

再换种写法,结果错误:
sql3:
select spid,cpid,acct_item_type_id,acct_item_type_name,sum(charge)
from tj_lmj_sp_1
where (spid is not null or spid <> 0)  --排除所有spid为0或为null的
  and (cpid is not null or cpid <> 0)  --排除所有cpid为0或为null的
group by  spid,cpid,acct_item_type_id,acct_item_type_name

楼主你看一下引用的文字,你存在这个疑问主要是你对逻辑需求转换为sql代码,有点问题,你上面不是说了吗:排除所有spid为0或为null的记录,并且排除所有cpid为0或为null的记录,当然是(spid is not null and spid <> 0)  
  and (cpid is not null and cpid <> 0) 怎么可能会是 (spid is not null or spid <> 0)
  and (cpid is not null or cpid <> 0) 这个呢,这两个根本就是不同的逻辑关系,楼主有点钻牛角啦
------解决方案--------------------
你直接ISNULL(spid,'0')不是很好?
------解决方案--------------------
之所以第3个语句是错误的是因为你的需求。

你是要:spid为0或为null的记录,并且排除所有cpid为0或为null的记录,那么spid就既不是0,也不是null,同时cpid也是一样的。

而第3个语句,你写成了:

(spid is not null or spid <> 0)  --排除所有spid为0或为null的
  and (cpid is not null or cpid <> 0)  --排除所有cpid为0或为null的

这就有问题了,比如spid是0,cpid是0,那么这个语句就错误了,为什么呢?

因为当spid和cpid都为0时,sql server 判断(spid is not null or spid <> 0)条件的时候,发现spid为0,那么就不是null,也就是满足spid is not null条件,于是条件成立,换句话说,他不会继续往后看or后面的条件了,也就是不会再看条件spid <> 0是否会满足。

所以对于or来说,只要第一个条件,也就是or之前的条件满足的时候,那么就不会看or后面的条件是否满足,所以导致了这个语句的错误。

而and是都会看,and前,and后,的条件都必须要满足的。