--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([x] varchar(6),[y] varchar(6))
insert [a]
select 'A','A1' union all
select 'B','B1' union all
select 'C','C1' --> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([x] varchar(6),[y] varchar(6))
insert [b]
select 'A','A2' union all
select 'C','C2' union all
select 'D','D2' --c查询语句
select o.xx,isnull(p.y,'')as py , isnull(q.y,'')as qy from
(
select distinct i.xx from
(
select a.x as xx from a
union all
select b.x as xx from b
) i
) o left join a p on o.xx=p.x
left join b q on o.xx=q.xxx py qy
------ ------ ------
A A1 A2
B B1
C C1 C2
D D2(4 行受影响)
解决方案 »
- 求助:基于远程网络远程数据库应用解决方案问题!!!
- oci bind 问题
- 谁知道linux oracle11的启动脚本,做成服务,如何写呢,请高手帮忙
- linux主机名问题
- 2006-3-31到2006-2-28到底算一个月还是2个月?用months_between计算时候算做一个月,对吗?
- 几个语句看不懂 请帮着解释下
- sqlserver2000链接Oracle9.02 表中包含BLOB类型字段,执行SELECT操作出错(不包含BLOB字段)
- 登陆SQLPLUS遇到的问题,麻烦大家予以指点,谢谢!
- 表数据的转换问题,请教
- spool是什么意思?哪位详细说明一下?
- 问个数据存储问题
- oracle定时job问题,求助
create table t4(id varchar2(10),name varchar2(10));
insert into t4 values('A','A1');
insert into t4 values('B','B1');
insert into t4 values('C','C1');
insert into t5 values('A','A2');
insert into t5 values('C','C2');
insert into t5 values('D','D2');SELECT nvl(id,id1),name,name1 FROM (
SELECT * FROM T4,T5 WHERE T4.ID= T5.ID1(+)
UNION
SELECT * FROM T4,T5 WHERE T4.ID(+)= T5.ID1
)
SELECT T1.C1,
(SELECT T2.C2 FROM TEST T2 WHERE T1.C1 = T2.C1) C2,
(SELECT T2.C2 FROM TEST1 T2 WHERE T1.C1 = T2.C1) C3
FROM (SELECT C1
FROM TEST
UNION
SELECT C1 FROM TEST1) T1
select 'A' c1,'A1' c2 from dual union all
select 'B','B1' from dual union all
select 'C','C1' from dual
),b as(
select 'A' c1,'A2' c2 from dual union all
select 'C','C2' from dual union all
select 'D','D2' from dual
)select decode(a.c1,null,b.c1,a.c1) c1,a.c2,b.c2 from a full join b on a.c1=b.c1 order by c1