create table test1
(
level1 varchar2(100),
level2 varchar2(100),
level3 varchar2(100)
);
insert into test1 values('test1','test2','');
insert into test1 values('test1','test2','test3');
select * from test1;select * from test1 t where t.level1='test1' and t.level2='test2' and t.level3 is null;
select * from test1 t where t.level1='test1' and t.level2='test2' and t.level3 = 'test3';--t.level3的值为外面动态传进来的。
--如果t.level3传进来有值的话,就跟上 and t.level3 = 'test3';
--如果t.level3传进来没有值的话,查询条件就跟上 and t.level3 is null; --不要用if判断来实现。if判断实现我会写。 就一条查询语句能否搞定?--这种没有值的情况下,这语句肯定查不出任何值。
select * from test1 t where t.level1='test1' and t.level2='test2' and t.level3 = '';
--没有值的情况下 ,可以查询出一条
select * from test1 t where t.level1='test1' and t.level2='test2' and (t.level3 = '' or t.level3 is null);
--有值的情况下 ,但是会出现两条值。 我只想出现那个level3有值的那条数据。
select * from test1 t where t.level1='test1' and t.level2='test2' and (t.level3 = 'test3' or t.level3 is null);
(
level1 varchar2(100),
level2 varchar2(100),
level3 varchar2(100)
);
insert into test1 values('test1','test2','');
insert into test1 values('test1','test2','test3');
select * from test1;select * from test1 t where t.level1='test1' and t.level2='test2' and t.level3 is null;
select * from test1 t where t.level1='test1' and t.level2='test2' and t.level3 = 'test3';--t.level3的值为外面动态传进来的。
--如果t.level3传进来有值的话,就跟上 and t.level3 = 'test3';
--如果t.level3传进来没有值的话,查询条件就跟上 and t.level3 is null; --不要用if判断来实现。if判断实现我会写。 就一条查询语句能否搞定?--这种没有值的情况下,这语句肯定查不出任何值。
select * from test1 t where t.level1='test1' and t.level2='test2' and t.level3 = '';
--没有值的情况下 ,可以查询出一条
select * from test1 t where t.level1='test1' and t.level2='test2' and (t.level3 = '' or t.level3 is null);
--有值的情况下 ,但是会出现两条值。 我只想出现那个level3有值的那条数据。
select * from test1 t where t.level1='test1' and t.level2='test2' and (t.level3 = 'test3' or t.level3 is null);
--假设传进来的参数是var_str
--这样写SQL:
select * from test1 t where t.level1='test1' and t.level2='test2'
(and t.level3 = 'test3' and var_str is not null) or (var_str is null and t.level3 is null);
SQL> select * from test1 where nvl(level3,'-') = decode('','test3','test3','','-');LEVEL1 LEVEL2 LEVEL3
---------- ---------- ----------
test1 test2SQL> select * from test1 where nvl(level3,'-') = decode('test3','test3','test3','','-');LEVEL1 LEVEL2 LEVEL3
---------- ---------- ----------
test1 test2 test3
--假设传进来的参数是var_str
--这样写SQL:
select *
from test1 t
where t.level1='test1' and t.level2='test2'
and (
(t.level3 = var_str and var_str is not null) or (var_str is null and t.level3 is null)
);