select t1.id,(
case when id is not null then t1.ksrq
case when id is null then to_date(max(t2.yksrq)||'-12-01','yyyy-mm-dd'
end)
from t1,t2 where t1.id=t2.id
case when id is not null then t1.ksrq
case when id is null then to_date(max(t2.yksrq)||'-12-01','yyyy-mm-dd'
end)
from t1,t2 where t1.id=t2.id
解决方案 »
- oracle expdp与exp 的速度问题
- oracle dbms_lob.writeappend救解
- <救命>oracle做了update的误操作,怎么恢复之前数据
- 在客户端用 SQL*PLUS WORKWHEET登陆数据库服务器提示错误,请高手指点.
- 同义词的表在存储过程编译时报错:table or view does not exist
- java.sql.SQLException: ORA-00903: 表名无效(急呀!不知道为什么)
- ORACLE9i中有 inventory 这个用户吗?我创建的数据库中怎么没有?
- 新手的问题--如何使用FOR语句操作游标.(在线)
- select 的用法
- 什么是数据库服务?什么是数据库服务方法?
- 创建实体化视图Sql求助??
- 怎样在存储过程实现组合条件查询
select decode(nvl(t1.ksrq,'NULL'),'NULL',t2.id,t1.id) id,
decode(nvl(t1.ksrq,'NULL'),'NULL',to_date('max(yksrq)-12-01','yyyy-mm-dd'),t1.ksrq) rq
from t1,t2 where t1.id=t2.id
create or replace view v1
(id , ksrq)
as
(select id,ksrq from t1
where id is not null
union
select t2.id,to_date('max(yksrq)-12-01','yyyy-mm-dd')
from t2,t1
where t1.id=t2.id
and t1.id is null)
假如你要的是内连接:
select t1.id,(
case when t1.ksrq is not null then t1.ksrq
when t1.ksrq is null then to_date(t3.ksrq||'-12-01','yyyy-mm-dd')
end) ksrq
from t1,(select id,max(yksrq) ksrq from t2 group by id) t3 where t1.id=t3.id
/
select *
from t1 where t1.ksrq is not null
union
select t2.id,to_date(max(t2.yksrq)||'-12-01','yyyy-mm-dd') as ksrq
from t1,t2 where t1.id=t2.id group by t2.id
create or replace view v1 as
select *
from t1 where t1.ksrq is not null
union
select t2.id,to_date(max(t2.yksrq)||'-12-01','yyyy-mm-dd') as ksrq
from t1,t2 where t1.id=t2.id and t1.ksrq is null group by t2.id
-----------------------------------------------------
我查了一下手册,发现8.1.7上可以使用case when