试试下面的方法,9i环境适用:
SQL> select * from test1; ID USERID
---------- --------------------
2 user008
3 user005
8 user007SQL> select * from test2; ID USERID
---------- ----------
1 user004
2 user002
5 user002以下是test1表不为空的情况:select* from
(select a.id ida,a.userid usera,b.id idb,b.userid userb, count(a.id) over() cnt
from test1 a full outer join test2 b
on a.id = b.id)
where (cnt = 0) or (cnt<>0 and usera is not null)SQL>
6 / IDA USERA IDB USERB CNT
---------- -------------------- ---------- ---------- ----------
2 user008 2 user002 3
8 user007 3
3 user005 3删除test1表中数据:SQL> delete from test1;3 rows deletedSQL> commit;Commit completeselect* from
(select a.id ida,a.userid usera,b.id idb,b.userid userb, count(a.id) over() cnt
from test1 a full outer join test2 b
on a.id = b.id)
where (cnt = 0) or (cnt<>0 and usera is not null)SQL>
6 / IDA USERA IDB USERB CNT
---------- -------------------- ---------- ---------- ----------
1 user004 0
5 user002 0
2 user002 0
SQL> select * from test1; ID USERID
---------- --------------------
2 user008
3 user005
8 user007SQL> select * from test2; ID USERID
---------- ----------
1 user004
2 user002
5 user002以下是test1表不为空的情况:select* from
(select a.id ida,a.userid usera,b.id idb,b.userid userb, count(a.id) over() cnt
from test1 a full outer join test2 b
on a.id = b.id)
where (cnt = 0) or (cnt<>0 and usera is not null)SQL>
6 / IDA USERA IDB USERB CNT
---------- -------------------- ---------- ---------- ----------
2 user008 2 user002 3
8 user007 3
3 user005 3删除test1表中数据:SQL> delete from test1;3 rows deletedSQL> commit;Commit completeselect* from
(select a.id ida,a.userid usera,b.id idb,b.userid userb, count(a.id) over() cnt
from test1 a full outer join test2 b
on a.id = b.id)
where (cnt = 0) or (cnt<>0 and usera is not null)SQL>
6 / IDA USERA IDB USERB CNT
---------- -------------------- ---------- ---------- ----------
1 user004 0
5 user002 0
2 user002 0
解决方案 »
- 求一个简单的单表查询~~~
- SSIS 连接ORACLE 以日期为查询条件的问题,请大家帮忙!在线等待中
- 寻求解决方案!关于Oracle查询速度的.
- 设定了参数,但在C#或JAVA中如何获取?
- 请教sql语句的问题
- 关于ORACLE日志的问题 高手赐教 有高手知道吗
- SQL语句有没有标准 ,为什么SQL SERVER和ORACLE的SQL语句语法不一样
- 如何配置联结ORACLE数据库的ODBC?
- 如何设置同时使用oracle数据库的用户数?
- sql plus 語句违反整合性限制条件ORA-02291: 違反整合性限制條件 (SYSTEM.SYS_C003039) 無法找到父項鍵
- 我想用 utl_file 来写文件 如果我想写入 某个盘下,并创建目录怎么办 在线等
- dbstart和dbshut之后无反应的问题
Oracle 9i中可以用CASE WHEN ,DECODE 等来做选择.你可以参考一下这方面的资料.
(select *,rownum as num from
(select t1,t2,t3 ...)) s1,
(select t1,t2,t3 ...) s2
where (s1.num is null and s1.t2(+)=s2.t2 )
or (s1.num>0 and s1.t2=s2.t2(+))这个可能可以达到你想要的效果.刚才说错了,除了select 后面,count应该只能用在having后面.
因为你是说求和,如果是SUM的话,那就只有用
select * from
(select *,sum(s1.t1) over() as num from
(select t1,t2,t3 ...)) s1,
(select t1,t2,t3 ...) s2
where (s1.num=0 and s1.t2(+)=s2.t2 )
or (s1.num<>0 and s1.t2=s2.t2(+))