select a,decode(b,null,"",b),decode(c,null,"",c) from tab1,tab2 where tab1.a=tab2.a
select a,decode(b,null,'""',b),decode(c,null,'""',c) from tab1,tab2 where tab1.a=tab2.a
select distinct tb1.a,decode(tb1.b,null,'',tb1.b),decode(tb2.c,null,'',tb2.c) from tb1,tb2没测试过
写错了,不过我觉得楼上的也不全对,好象要分几种情况处理select distinct a from tb1;个数为n select distinct a from tb2;个数为m1 n>m2 n<m3 n=m
1 n>m: select tb1.a,decode(tb1.b,null,'',tb1.b),decode(tb2.c,null,'',tb2.c) from tb1,tb2 where tb1.a=tb2.a(+) 2 n<m: select tb1.a,decode(tb1.b,null,'',tb1.b),decode(tb2.c,null,'',tb2.c) from tb1,tb2 where tb1.a(+)=tb2.a3 n=m: select tb1.a,decode(tb1.b,null,'',tb1.b),decode(tb2.c,null,'',tb2.c) from tb1,tb2 where tb1.a=tb2.a
nvl 或者decodeselect TB1.A, NVL(TB1.B,'""'), NVL(TB1.B,'""') from ....
忘了说明,要求最后查出来的效果: A B C A1 B1 C1 A1 B2 C2 A1 "" C3 A2 B1 C1 A2 B2 C2 A2 B3 "" A3 B3 "" ... ... ... 所以右连接是必须的,但是楼上写的SQL做过测试么?
sorry,打开的比较早,回的是 bluelamb(bluelamb) 那段。
bluelamb(bluelamb)的好象是内连接啊
try:select tb1.a,decode(tb1.b,null,'',tb1.b),decode(tb2.c,null,'',tb2.c) from tb1,tb2 where tb1.a=tb2.a(+)
测试结果: test 表 ca cb A1 B1 A1 B2 A1 B1 A2 B1 A2 B2 A2 B2 A2 B2 A3 B1test_1表 a b A1 B A1 B A1 B A2 B A2 B执行sql: SELECT CA, DECODE(CB,NULL,'""',CB), DECODE(B,NULL,'""',B) FROM TEST,TEST_1 WHERE CA = A(+) 执行结果: ca compute_0002 compute_0003 A1 B1 B A1 B1 B A1 B1 B A1 B2 B A1 B2 B A1 B2 B A1 B1 B A1 B1 B A1 B1 B A2 B1 B A2 B1 B A2 B2 B A2 B2 B A2 B2 B A2 B2 B A2 B2 B A2 B2 B A3 B1 ""结果是错误的。
那是因为你的原始数据中有重复的啊改为select distinct tb1.a,decode(tb1.b,null,'',tb1.b),decode(tb2.c,null,'',tb2.c) from tb1,tb2 where tb1.a=tb2.a(+)
SELECT DISTINCT CA, DECODE(CB,NULL,'""',CB), DECODE(B,NULL,'""',B) FROM TEST,TEST_1 WHERE CA(+) = A(+)Is it that ok? I have not given a whirl as my oracle server is down...
test_1 a b A1 Ba A1 Bb A1 Bc A2 Ba A2 Bbtest ca cb A1 B1 A1 B2 A2 B1 A2 B2 A3 B1执行sql: SELECT distinct CA, DECODE(CB,NULL,'""',CB) as cb, DECODE(B,NULL,'""',B) as b FROM TEST,TEST_1 WHERE CA = A(+)结果 ca cb b A1 B1 Ba A1 B1 Bb A1 B1 Bc A1 B2 Ba A1 B2 Bb A1 B2 Bc A2 B1 Ba A2 B1 Bb A2 B2 Ba A2 B2 Bb A3 B1 ""还是不对。呵呵,这不象想的那样简单。
what's the result that you wanna to get? The key problem is that we can not understand your requirement and your business logic...
test_1 a b A1 Ba A1 Bb A1 Bc A2 Ba A2 Bb A4 Batest ca cb A1 B1 A1 B2 A2 B1 A2 B2 A3 B1Result: ca cb b A1 B1 Ba A1 B2 Bb A1 "" Bc A2 B1 Ba A2 B2 Bb A3 B1 "" A4 "" Ba
try: select nvl(ca,a) ca,nvl(cb,'""') cb,nvl(b,'""') b from (select a.*,row_number() over(partition by a order by rowid) rid1 from test_1 a) t full outer join (select b.*,row_number() over(partition by ca order by rowid) rid2 from test_2 b) tt on (t.a=tt.ca and t.rid1=tt.rid2);
select nvl(tb1.a,tb2.a) a, nvl(tb1.b,"") b,nvl(tb2.c,"") c from tb1 out join tb2 on tb1.a = tb2.a
to bzszp(SongZip) ( )over(partition by...)这是什么版本的语法,是什么意思,我只用过8.0.5,不好意思。
执行: select nvl(ca,a) ca,nvl(cb,'""') cb,nvl(b,'""') b from (select a.*,row_number() over(partition by a order by rowid) rid1 from test_1 a) t full outer join (select b.*,row_number() over(partition by ca order by rowid) rid2 from test b) tt on (t.a=tt.ca and t.rid1=tt.rid2) ORDER BY CA Result: ca cb b A1 B1 Ba A1 B2 Bb A1 """""" Bb A1 """""" Ba A1 """""" Bc A2 B1 Ba A2 B2 Bb A2 """""" Ba A2 """""" Bb A3 B1 """""" A4 """""" Ba还差一点点
8.1.6测试:由于不支持full outer join,因此,用两个外连结 17:23:33 SQL> select nvl(ca,a) ca,nvl(cb,'""') cb,nvl(b,'""') b from 17:23:40 2 (select a.*,row_number() over(partition by a order by rowid) rid1 from test_1 a) t, 17:23:40 3 (select b.*,row_number() over(partition by ca order by rowid) rid2 from test_2 b) tt 17:23:40 4 where t.a=tt.ca(+) and t.rid1=tt.rid2(+) 17:23:40 5 union 17:23:40 6 select nvl(ca,a) ca,nvl(cb,'""') cb,nvl(b,'""') b from 17:23:40 7 (select a.*,row_number() over(partition by a order by rowid) rid1 from test_1 a) t, 17:23:40 8 (select b.*,row_number() over(partition by ca order by rowid) rid2 from test_2 b) tt 17:23:40 9 where t.a(+)=tt.ca and t.rid1(+)=tt.rid2;CA CB B ---------- ---------- ---------- A1 "" Bc A1 B1 Bb A1 B2 Ba A2 B1 Ba A2 B2 Bb A3 B1 "" A4 "" Ba已选择7行。已用时间: 00: 00: 00.46 17:23:40 SQL> 测试通过。
where tab1.a=tab2.a
where tab1.a=tab2.a
select distinct a from tb2;个数为m1 n>m2 n<m3 n=m
2 n<m: select tb1.a,decode(tb1.b,null,'',tb1.b),decode(tb2.c,null,'',tb2.c) from tb1,tb2 where tb1.a(+)=tb2.a3 n=m: select tb1.a,decode(tb1.b,null,'',tb1.b),decode(tb2.c,null,'',tb2.c) from tb1,tb2 where tb1.a=tb2.a
A B C
A1 B1 C1
A1 B2 C2
A1 "" C3
A2 B1 C1
A2 B2 C2
A2 B3 ""
A3 B3 ""
... ... ...
所以右连接是必须的,但是楼上写的SQL做过测试么?
test 表
ca cb
A1 B1
A1 B2
A1 B1
A2 B1
A2 B2
A2 B2
A2 B2
A3 B1test_1表
a b
A1 B
A1 B
A1 B
A2 B
A2 B执行sql:
SELECT CA,
DECODE(CB,NULL,'""',CB),
DECODE(B,NULL,'""',B)
FROM TEST,TEST_1
WHERE CA = A(+)
执行结果:
ca compute_0002 compute_0003
A1 B1 B
A1 B1 B
A1 B1 B
A1 B2 B
A1 B2 B
A1 B2 B
A1 B1 B
A1 B1 B
A1 B1 B
A2 B1 B
A2 B1 B
A2 B2 B
A2 B2 B
A2 B2 B
A2 B2 B
A2 B2 B
A2 B2 B
A3 B1 ""结果是错误的。
A2 应该出现4次
只有A3正确,因为右连接未关联上
而是全外连接或是Cartesian Product的结果的说...
函数可以用NVL或者DECODE
union 是纵向的合并,现在我需要的是按某规则横向的合并。
DECODE(CB,NULL,'""',CB),
DECODE(B,NULL,'""',B)
FROM TEST,TEST_1
WHERE CA(+) = A(+)Is it that ok? I have not given a whirl as my oracle server is down...
a b
A1 Ba
A1 Bb
A1 Bc
A2 Ba
A2 Bbtest
ca cb
A1 B1
A1 B2
A2 B1
A2 B2
A3 B1执行sql:
SELECT distinct CA,
DECODE(CB,NULL,'""',CB) as cb,
DECODE(B,NULL,'""',B) as b
FROM TEST,TEST_1
WHERE CA = A(+)结果
ca cb b
A1 B1 Ba
A1 B1 Bb
A1 B1 Bc
A1 B2 Ba
A1 B2 Bb
A1 B2 Bc
A2 B1 Ba
A2 B1 Bb
A2 B2 Ba
A2 B2 Bb
A3 B1 ""还是不对。呵呵,这不象想的那样简单。
The key problem is that we can not understand your requirement and your business logic...
a b
A1 Ba
A1 Bb
A1 Bc
A2 Ba
A2 Bb
A4 Batest
ca cb
A1 B1
A1 B2
A2 B1
A2 B2
A3 B1Result:
ca cb b
A1 B1 Ba
A1 B2 Bb
A1 "" Bc
A2 B1 Ba
A2 B2 Bb
A3 B1 ""
A4 "" Ba
select nvl(ca,a) ca,nvl(cb,'""') cb,nvl(b,'""') b
from
(select a.*,row_number() over(partition by a order by rowid) rid1 from test_1 a) t full outer join
(select b.*,row_number() over(partition by ca order by rowid) rid2 from test_2 b) tt on (t.a=tt.ca and t.rid1=tt.rid2);
from tb1 out join tb2 on tb1.a = tb2.a
执行:
select nvl(ca,a) ca,nvl(cb,'""') cb,nvl(b,'""') b
from
(select a.*,row_number() over(partition by a order by rowid) rid1 from test_1 a) t
full outer join
(select b.*,row_number() over(partition by ca order by rowid) rid2 from test b) tt
on (t.a=tt.ca and t.rid1=tt.rid2)
ORDER BY CA
Result:
ca cb b
A1 B1 Ba
A1 B2 Bb
A1 """""" Bb
A1 """""" Ba
A1 """""" Bc
A2 B1 Ba
A2 B2 Bb
A2 """""" Ba
A2 """""" Bb
A3 B1 """"""
A4 """""" Ba还差一点点
17:23:33 SQL> select nvl(ca,a) ca,nvl(cb,'""') cb,nvl(b,'""') b from
17:23:40 2 (select a.*,row_number() over(partition by a order by rowid) rid1 from test_1 a) t,
17:23:40 3 (select b.*,row_number() over(partition by ca order by rowid) rid2 from test_2 b) tt
17:23:40 4 where t.a=tt.ca(+) and t.rid1=tt.rid2(+)
17:23:40 5 union
17:23:40 6 select nvl(ca,a) ca,nvl(cb,'""') cb,nvl(b,'""') b from
17:23:40 7 (select a.*,row_number() over(partition by a order by rowid) rid1 from test_1 a) t,
17:23:40 8 (select b.*,row_number() over(partition by ca order by rowid) rid2 from test_2 b) tt
17:23:40 9 where t.a(+)=tt.ca and t.rid1(+)=tt.rid2;CA CB B
---------- ---------- ----------
A1 "" Bc
A1 B1 Bb
A1 B2 Ba
A2 B1 Ba
A2 B2 Bb
A3 B1 ""
A4 "" Ba已选择7行。已用时间: 00: 00: 00.46
17:23:40 SQL> 测试通过。