现在一张表,字段如下
ID ANAME ATJ AVALUE
1 a 1 11
2 b -1 -11
3 c 0 100
4 d 1 111
如果atj=1则执行id>1
如果atj=-1则执行id<3
如果atj=0则执行(id>1 or id<3)
下面我做的是不对的,请问高手门如何实现
select case when atj=1 then id>1
case when atj=-1 then id<3
case when atj=0 then (id>1 or id<3)
from aaa
ID ANAME ATJ AVALUE
1 a 1 11
2 b -1 -11
3 c 0 100
4 d 1 111
如果atj=1则执行id>1
如果atj=-1则执行id<3
如果atj=0则执行(id>1 or id<3)
下面我做的是不对的,请问高手门如何实现
select case when atj=1 then id>1
case when atj=-1 then id<3
case when atj=0 then (id>1 or id<3)
from aaa
字段atj=-1时,执行select * from aaa where id<3
字段atj=0时,执行select * from aaa where id>1 or id<3
from ATJ a
where a.atj = 1
and a.id > 1
union
select a.*
from ATJ a
where a.atj = -1
and a.id < 3
union
select a.*
from ATJ a
where a.atj = 0
and a.id > 1
and a.id < 3;
结果:--------------------------------- ID ANAME ATJ AVALUE
---------- ----- ---------- ----------
2 b -1 -11
4 d 1 111
结果应该是这样的
2 b -1 -11
3 c 0 100
4 d 1 111
(atj=-1 AND id<3) OR (atj=0 AND (id>1 or id<3))
我是想接收一个参数,参数值=1时,执行select * from aaa where id>1
参数值=-1时,执行select * from aaa where id<3
参数值=0时,执行select * from aaa where id>1 or id<3
结果应该是这样的2 b -1 -11
3 c 0 100
4 d 1 111
FROM aaa
where (id>1 or (:atj<>1))
and (id<3 or (:atj<>-1))
and ((id>1 or id<3) or (:atj<>0))
--atj为参数
select * from aaa
where (id>1 and :atj=1)
or (id<3 and :atj=-1)
or ((id >1 or id <3) and :atj=0);
select * from aaa
where (id>1 and :atj=1)
or (id<3 and :atj=-1)
or ((id >1 and id <3) and :atj=0);