要实现的是, 查询一个表,按某个条件
如果,条件1有数据,就取条件1的数据,
如果,条件1没有数据,就取条件2的数据.伪句如下:
case when
(select count(*) from tabletest where domain = 'test1') > 0
then
(select * from tabletest where domain = 'test1')
else
(select * from tabletest where domain = 'test2')
end求,不用存储过程的情况下,有实现的办法吗?
先谢谢了~
如果,条件1有数据,就取条件1的数据,
如果,条件1没有数据,就取条件2的数据.伪句如下:
case when
(select count(*) from tabletest where domain = 'test1') > 0
then
(select * from tabletest where domain = 'test1')
else
(select * from tabletest where domain = 'test2')
end求,不用存储过程的情况下,有实现的办法吗?
先谢谢了~
not exists (select * from tabletest where domain = 'test1' ))
ID DOMAIN
---------- --------------------
1 test1
2 test1
3 test2
4 test2
5 test2
SQL>
SQL> select * from t1
2 where domain =
3 case when (select count(*) from t1 where domain = 'test1') > 0 then 'test1' else 'test2' end;
ID DOMAIN
---------- --------------------
1 test1
2 test1
SQL> delete from t1 where domain = 'test1';
2 rows deleted
SQL>
SQL> select * from t1
2 where domain =
3 case when (select count(*) from t1 where domain = 'test1') > 0 then 'test1' else 'test2' end;
ID DOMAIN
---------- --------------------
3 test2
4 test2
5 test2
SQL>
where domain =
case when (select count(*) from t1 where domain = 'test1') > 0 then 'test1' else 'test2' end;
insert into tabletest(id,domain) values(1,'test1');
insert into tabletest(id,domain) values(2,'test1');
insert into tabletest(id,domain) values(3,'test1');
insert into tabletest(id,domain) values(4,'test2');select * from tabletest
where domain = ( select (case when t.t1>0 then 'test1' else 'test2' end) from
( select sum(case when domain='test1' then 1 else 0 end) as t1
from tabletest ) t );delete from tabletest where domain='test1';select * from tabletest
where domain = ( select (case when t.t1>0 then 'test1' else 'test2' end) from
( select sum(case when domain='test1' then 1 else 0 end) as t1
from tabletest ) t );
where domain=( select domain
from (select distinct domain
from tabletest
where domain in ('test1','test2') ) t
where rownum=1);delete from tabletest where domain='test1';select * from tabletest
where domain=( select domain
from (select distinct domain
from tabletest
where domain in ('test1','test2') ) t
where rownum=1);
decode((select 'X' from tabletest where domain = 'test1') ,'X','test1','test2');
select * from tabletest where domain =
decode((select 'X' from tabletest where domain = 'test1' and rownum=1) ,'X','test1','test2');