>or后面的and是否有效 有效呀,你的查询结果不加and的多,加了and的少。 ‘or’和‘and’逻辑处理级别相等,所以哪一个的顺序靠前就先执行哪一个。 下面2个sql可以说明:select sysdate from dual where 1=0 or 1=1 and 1=0 --------------------不等于 select sysdate from dual where 1=0 and 1=0 or 1=1
呵呵,语句没有问题啊: 对第一条sql语句应该是满足: 1. wki_state in(1,2,4) And (wki_state='1' And wka_applyman Is Null) 2. ((atd_tamid =1 And wki_state='4' and wka_applyman Is Null) Or (atd_tamid =2 And wki_state='4' and wka_applyman=wki_user)) 3. (wki_user Is Null And wki_state='2') and area_id='01' 3个条件中任意一个的纪录总数对第二个sql语句是满足: 1. wki_state in(1,2,4) And (wki_state='1' And wka_applyman Is Null) and area_id='01' 2. ((atd_tamid =1 And wki_state='4' and wka_applyman Is Null) Or (atd_tamid =2 And wki_state='4' and wka_applyman=wki_user)) 3. (wki_user Is Null And wki_state='2') 3个条件中任意一个的纪录总数只有第二个条件是一致,而第一个sql语句的第一个条件查询出来的纪录多于第二个sql语句查询出来的纪录;第一个sql语句的第三个条件查询出来的纪录少于第二个sql语句查询出来的纪录; 这并不能说明第二个sql语句查询出的纪录总数就必须少于第一个啊
楼上大哥,我估计以前的开发人员的想法是必须先排除area_id这个条件,然后再判断其他条件,因此他的这条语句应该是写错了,这么写应该才是正确的吧 Select Count(*) from lamis.view_tasklist Where (wki_state in(1,2,4) and (wki_state='1' And wka_applyman Is Null)
Or ((atd_tamid =1 And wki_state='4' and wka_applyman Is Null) Or (atd_tamid =2 And wki_state='4' and wka_applyman=wki_user)) Or (wki_user Is Null And wki_state='2')) and area_id='01'
但是要改成union,是不是这么改的: Select Count(*) From ( select * from lamis.view_tasklist Where wki_state In (1,2,4) And (wki_state='1' And wka_applyman Is Null) Union select * from lamis.view_tasklist Where atd_tamid =1 And wki_state='4' and wka_applyman Is Null Union select * from lamis.view_tasklist Where atd_tamid =2 And wki_state='4' and wka_applyman=wki_user Union Select * from lamis.view_tasklist Where wki_user Is Null And wki_state='2' ) a Where a.area_id='01'
我的以下回答可能不正确: -----------------------> ‘or’和‘and’逻辑处理级别相等,所以哪一个的顺序靠前就先执行哪一个。 -----------------------<另外,优化了一下你的sql,试一试: Select Count(*) from lamis.view_tasklist Where /*wki_state in (1, 2, 4) And */ ( (wki_state = '1' And wka_applyman Is Null) Or
/* ((atd_tamid = 1 And wki_state = '4' and wka_applyman Is Null) Or (atd_tamid = 2 And wki_state = '4' and wka_applyman = wki_user)) */
( wki_state = '4' and ((atd_tamid = 1 And and wka_applyman Is Null) Or (atd_tamid = 2 and wka_applyman = wki_user)) )
Or (wki_user Is Null And wki_state = '2') ) and area_id = '01'
to:killhuore() 你改成union的sql,第一个字查询可以简化一下:select * from lamis.view_tasklist Where wki_state In (1,2,4) And (wki_state='1' And wka_applyman Is Null) -----> select * from lamis.view_tasklist Where wki_state='1' And wka_applyman Is Null
大哥们,我最后整理的语句,你们看是不是以前开发人员的思路? Select Count(*) From ( select * from lamis.view_tasklist Where (wki_state='1' And wka_applyman Is Null) Union select * from lamis.view_tasklist Where atd_tamid =1 And wki_state='4' and wka_applyman Is Null Union select * from lamis.view_tasklist Where atd_tamid =2 And wki_state='4' and wka_applyman=wki_user Union Select * from lamis.view_tasklist Where wki_user Is Null And wki_state='2' ) a Where a.area_id='02' And a.wki_state In (1,2,4)
to:笨猫儿 >怎么说and 的优先级也是高于or的吧 我查了一下oracle的sql优先级问题,好像是你说的对。 not>and>or楼主看一下我上面的回复。另外,以后尽量避免下面这样的条件出现: where a1=a2 or a3=a4 and a5=a6 尽量加上括号,如: where (a1=a2 or a3=a4) and a5=a6 或 where a1=a2 or (a3=a4 and a5=a6)
有效呀,你的查询结果不加and的多,加了and的少。
‘or’和‘and’逻辑处理级别相等,所以哪一个的顺序靠前就先执行哪一个。
下面2个sql可以说明:select sysdate
from dual
where 1=0 or 1=1 and 1=0
--------------------不等于
select sysdate
from dual
where 1=0 and 1=0 or 1=1
对第一条sql语句应该是满足:
1. wki_state in(1,2,4) And (wki_state='1' And wka_applyman Is Null)
2. ((atd_tamid =1 And wki_state='4' and wka_applyman Is Null) Or (atd_tamid =2 And wki_state='4' and wka_applyman=wki_user))
3. (wki_user Is Null And wki_state='2') and area_id='01'
3个条件中任意一个的纪录总数对第二个sql语句是满足:
1. wki_state in(1,2,4) And (wki_state='1' And wka_applyman Is Null) and area_id='01'
2. ((atd_tamid =1 And wki_state='4' and wka_applyman Is Null) Or (atd_tamid =2 And wki_state='4' and wka_applyman=wki_user))
3. (wki_user Is Null And wki_state='2')
3个条件中任意一个的纪录总数只有第二个条件是一致,而第一个sql语句的第一个条件查询出来的纪录多于第二个sql语句查询出来的纪录;第一个sql语句的第三个条件查询出来的纪录少于第二个sql语句查询出来的纪录;
这并不能说明第二个sql语句查询出的纪录总数就必须少于第一个啊
Select Count(*)
from lamis.view_tasklist
Where (wki_state in(1,2,4) and
(wki_state='1' And wka_applyman Is Null)
Or ((atd_tamid =1 And wki_state='4' and wka_applyman Is Null)
Or (atd_tamid =2 And wki_state='4' and wka_applyman=wki_user))
Or (wki_user Is Null And wki_state='2')) and area_id='01'
Select Count(*) From (
select * from lamis.view_tasklist Where wki_state In (1,2,4) And (wki_state='1' And wka_applyman Is Null)
Union
select * from lamis.view_tasklist Where atd_tamid =1 And wki_state='4' and wka_applyman Is Null
Union
select * from lamis.view_tasklist Where atd_tamid =2 And wki_state='4' and wka_applyman=wki_user
Union
Select * from lamis.view_tasklist Where wki_user Is Null And wki_state='2'
) a Where a.area_id='01'
----------------------->
‘or’和‘and’逻辑处理级别相等,所以哪一个的顺序靠前就先执行哪一个。
-----------------------<另外,优化了一下你的sql,试一试:
Select Count(*)
from lamis.view_tasklist
Where /*wki_state in (1, 2, 4)
And */
(
(wki_state = '1' And wka_applyman Is Null) Or
/* ((atd_tamid = 1 And wki_state = '4' and wka_applyman Is Null) Or
(atd_tamid = 2 And wki_state = '4' and wka_applyman = wki_user)) */
( wki_state = '4' and
((atd_tamid = 1 And and wka_applyman Is Null) Or (atd_tamid = 2 and wka_applyman = wki_user))
)
Or (wki_user Is Null And wki_state = '2')
)
and area_id = '01'
你改成union的sql,第一个字查询可以简化一下:select * from lamis.view_tasklist Where wki_state In (1,2,4) And (wki_state='1' And wka_applyman Is Null)
----->
select * from lamis.view_tasklist Where wki_state='1' And wka_applyman Is Null
Select Count(*) From (
select * from lamis.view_tasklist Where
(wki_state='1' And wka_applyman Is Null)
Union
select * from lamis.view_tasklist Where atd_tamid =1 And wki_state='4' and wka_applyman Is Null
Union
select * from lamis.view_tasklist Where atd_tamid =2 And wki_state='4' and wka_applyman=wki_user
Union
Select * from lamis.view_tasklist Where wki_user Is Null And wki_state='2'
) a Where a.area_id='02' And a.wki_state In (1,2,4)
licsth() 和xiaoxiao1984(笨猫儿) 给分吧,wiler(@_@) 大哥没意见吧?
>怎么说and 的优先级也是高于or的吧
我查了一下oracle的sql优先级问题,好像是你说的对。
not>and>or楼主看一下我上面的回复。另外,以后尽量避免下面这样的条件出现:
where a1=a2 or a3=a4 and a5=a6
尽量加上括号,如:
where (a1=a2 or a3=a4) and a5=a6
或
where a1=a2 or (a3=a4 and a5=a6)