SQL求助

[一页扁舟]

有如下两张表
A TABLE
ID ID_VERSION
1000 1
1000 2
1000 3

B TABLE
ID ID_NUM
1000 3
1000 4

求结果表如下
RESULT
ID ID_VERSION ID_NUM
1000 1
1000 2
1000 3 3
1000 4

各位大虾，如何写相应的SQL文呢？

[yixilan]

看不清楚你的结合规律。
虽然表面上大概知道。但是希望描述清楚。

[大明湖畔_帅锅]

有什么规则？ 结果是如何得来的呢？ 2表合并 有重复的就添加在后面？

[一页扁舟]

1.显示A表B表所有ID一致的数据
2.在1的基础上，A.ID_VERSION = B.ID_NUM的场合下，显示为一条记录 ,例
ID, ID_VERSION, ID_NUM
1000, 3, 3
3.在1的基础上，当A.ID_VERSION <> B.ID_NUM的场合下，显示两条记录， 例
ID, ID_VERSION, ID_NUM
1000, 1, ""
1000, "", 4

[xpingping]

select A.*,B.ID_NUM
from A full outer join B 
on A.ID_VERSION=b.ID_NUM

[大明湖畔_帅锅]

create table test1(id number(5),ID_VERSION number(2));
insert into test1 values(1000,1);
insert into test1 values(1000,2);
insert into test1 values(1000,3);
insert into test1 values(1000,4);
insert into test1 values(1000,5);
insert into test1 values(2000,5);


create table test2(id number(5),ID_NUM number(2));
insert into test2 values(1000,3);
insert into test2 values(1000,4);
insert into test2 values(2000,4);
insert into test2 values(2000,5);

select case when a.id is null then b.id else a.id end id,
       a.id_version,b.id_num 
from test1 a full join test2 b on a.id=b.id and a.id_version=b.id_num
order by a.id,a.id_version


       id   ID_VERSION id_num 
-----------------------------------------
1	1000	1	
2	1000	2	
3	1000	3	3
4	1000	4	4
5	1000	5	
6	2000	5	5
7	2000		4

[wangpengpengwang]

您这个是写SQL还是用短程序实现啊？

[流氓你怕谁]

[Quote=引用 5 楼 hj_daxian 的回复:]

SQL code

create table test1(id number(5),ID_VERSION number(2));
insert into test1 values(1000,1);
insert into test1 values(1000,2);
insert into test1 values(1000,3);
insert into test1 values(1000,4……
[/Quote]


up

[Hi幸福来敲门]

自己寫的 5樓的更好

select a.id,a.ID_VERSION,decode(b.ID_NUM,a.ID_VERSION,a.ID_VERSION,'') from (
select xf_b.id,ID_NUM from xf_b,xf_a where xf_a.id=xf_b.id and xf_a.ID_VERSION=xf_b.ID_NUM) b right join (

select id,ID_VERSION from xf_a union
select id,ID_NUM from xf_b) a on a.id=b.id order by a.ID_VERSION