查看: 26392|回复: 94

[原创] 测试一下你的Oracle有Full Outer Join的Bug么?9i以上请进

[复制链接]
招聘 : 系统分析师
论坛徽章:
483
马上有钱
日期:2014-02-19 11:55:14itpub13周年纪念徽章
日期:2014-09-29 01:14:14itpub13周年纪念徽章
日期:2014-10-08 15:15:25itpub13周年纪念徽章
日期:2014-10-08 15:15:25马上有对象
日期:2014-10-12 11:58:40马上有车
日期:2014-11-16 17:11:29慢羊羊
日期:2015-02-09 17:04:38沸羊羊
日期:2015-03-04 14:43:432015年新春福章
日期:2015-03-06 11:57:31ITPUB年度最佳版主
日期:2015-03-18 15:48:48
发表于 2005-11-22 13:26 | 显示全部楼层 |阅读模式
测试一下你的Oracle有Full Outer Join的Bug么?9i以上请进

BUG描述:FULL OUTER JOIN <> LEFT OUTER JOIN UNION RIGHT OUTER JOIN
不知道Oracle网站上有没有相关描述,我在网上相关信息只搜索到left outer join可使用户可以查询任何表

我的数据库服务器环境:OS:Windows 2000 Server  DB:Oracle 9.2.0.1
测试请写出你的数据库服务器环境

创建测试表
--子表
CREATE TABLE TS
(
  ID   VARCHAR2(4 BYTE),
  PID  VARCHAR2(4 BYTE),
  DM1  VARCHAR2(4 BYTE),
  DM2  VARCHAR2(4 BYTE)
);
--父表,ID与子表PID对应
CREATE TABLE TP
(
  ID   VARCHAR2(4 BYTE),
  CON  VARCHAR2(4 BYTE)
);
--代码表
CREATE TABLE TDM
(
  TYPE  VARCHAR2(4 BYTE),
  DM    VARCHAR2(4 BYTE),
  MC    VARCHAR2(10 BYTE)
);
插入测试数据
INSERT INTO TS ( ID, PID, DM1, DM2 ) VALUES (
'1', '1', 'a', 'aa');
INSERT INTO TS ( ID, PID, DM1, DM2 ) VALUES (
'2', '2', 'b', NULL);
INSERT INTO TS ( ID, PID, DM1, DM2 ) VALUES (
'3', '1', 'a', 'ss');
INSERT INTO TS ( ID, PID, DM1, DM2 ) VALUES (
'4', '3', 'a', 'ss');
INSERT INTO TS ( ID, PID, DM1, DM2 ) VALUES (
'5', '3', 'c', NULL);
INSERT INTO TS ( ID, PID, DM1, DM2 ) VALUES (
'7', '7', 'c', 'ee');
INSERT INTO TS ( ID, PID, DM1, DM2 ) VALUES (
'9', NULL, 'a', NULL);
INSERT INTO TS ( ID, PID, DM1, DM2 ) VALUES (
'10', '9', NULL, NULL);
COMMIT;
INSERT INTO TP ( ID, CON ) VALUES (
'1', 'a');
INSERT INTO TP ( ID, CON ) VALUES (
'2', 'b');
INSERT INTO TP ( ID, CON ) VALUES (
'3', 'c');
INSERT INTO TP ( ID, CON ) VALUES (
'6', 'f');
COMMIT;
INSERT INTO TDM ( TYPE, DM, MC ) VALUES (
'1', 'a', 'AA');
INSERT INTO TDM ( TYPE, DM, MC ) VALUES (
'1', 'b', 'BB');
INSERT INTO TDM ( TYPE, DM, MC ) VALUES (
'1', 'c', 'CC');
INSERT INTO TDM ( TYPE, DM, MC ) VALUES (
'1', 'd', 'DD');
INSERT INTO TDM ( TYPE, DM, MC ) VALUES (
'1', 'e', 'EE');
INSERT INTO TDM ( TYPE, DM, MC ) VALUES (
'2', 'aa', 'AAAA');
INSERT INTO TDM ( TYPE, DM, MC ) VALUES (
'2', 'ss', 'SSSS');
INSERT INTO TDM ( TYPE, DM, MC ) VALUES (
'2', 'dd', 'DDDD');
COMMIT;
表TS与表TDM外连接两次,查出DM1和DM2对应的MC
select a.*,b.mc MC1, c.mc MC2
from ts a,tdm b, tdm c
where  A.dm1 = B.DM(+) AND B.type(+)='1'
and  A.dm2 = c.DM(+) AND c.type(+)='2'
这个sql将会在下面的测试中多次用到
(其实只要3和6都做了就能看到这个BUG)
1)测试LEFT OUTER JOIN
select s.*,p.* from
(
select a.*,b.mc MC1, c.mc MC2 from ts a,tdm b, tdm c
where  A.dm1 = B.DM(+) AND B.type(+)='1'
and  A.dm2 = c.DM(+) AND c.type(+)='2'
) s left outer join
(
select * from tp
)  p
on s.pid=p.id
2)测试RIGHT OUTER JOIN
select s.*,p.* from
(
select a.*,b.mc MC1, c.mc MC2 from ts a,tdm b, tdm c
where  A.dm1 = B.DM(+) AND B.type(+)='1'
and  A.dm2 = c.DM(+) AND c.type(+)='2'
) s right outer join
(
select * from tp
)  p
on s.pid=p.id
3)测试LEFT OUTER JOIN UNION RIGHT OUTER JOIN
select s.*,p.* from
(
select a.*,b.mc MC1, c.mc MC2 from ts a,tdm b, tdm c
where  A.dm1 = B.DM(+) AND B.type(+)='1'
and  A.dm2 = c.DM(+) AND c.type(+)='2'
) s left outer join
(
select * from tp
)  p
on s.pid=p.id  
union  
select s.*,p.* from
(
select a.*,b.mc MC1, c.mc MC2 from ts a,tdm b, tdm c
where  A.dm1 = B.DM(+) AND B.type(+)='1'
and  A.dm2 = c.DM(+) AND c.type(+)='2'
) s right outer join
(
select * from tp
)  p
on s.pid=p.id
4)测试LEFT OUTER JOIN UNION ALL RIGHT OUTER JOIN
select s.*,p.* from
(
select a.*,b.mc MC1, c.mc MC2 from ts a,tdm b, tdm c
where  A.dm1 = B.DM(+) AND B.type(+)='1'
and  A.dm2 = c.DM(+) AND c.type(+)='2'
) s left outer join
(
select * from tp
)  p
on s.pid=p.id  
union all
select s.*,p.* from
(
select a.*,b.mc MC1, c.mc MC2 from ts a,tdm b, tdm c
where  A.dm1 = B.DM(+) AND B.type(+)='1'
and  A.dm2 = c.DM(+) AND c.type(+)='2'
) s right outer join
(
select * from tp
)  p
on s.pid=p.id
5)测试内连接(INNER JOIN)
select s.* from
(
select a.*,b.mc MC1, c.mc MC2 from ts a,tdm b, tdm c
where  A.dm1 = B.DM(+) AND B.type(+)='1'
and  A.dm2 = c.DM(+) AND c.type(+)='2'
) s inner  join
(
select * from tp
)  p
on s.pid=p.id
6)测试全外连接(FULL OUTER JOIN)
select s.*,p.* from
(
select a.*,b.mc MC1, c.mc MC2
from ts a,tdm b, tdm c
where  A.dm1 = B.DM(+) AND B.type(+)='1'
and  A.dm2 = c.DM(+) AND c.type(+)='2'
) s full outer join
(
select * from tp
)  p
on s.pid=p.id
下面是测试结果(只列举了3和6)
[php]
SQL> select * from ts;

ID   PID  DM1  DM2
---- ---- ---- ----
1    1    a    aa
2    2    b
3    1    a    ss
4    3    a    ss
5    3    c
7    7    c    ee
9         a
10   9

已选择8行。

SQL> select * from tp;

ID   CON
---- ----
1    a
2    b
3    c
6    f

SQL> select * from tdm;

TYPE DM   MC
---- ---- ----------
1    a    AA
1    b    BB
1    c    CC
1    d    DD
1    e    EE
2    aa   AAAA
2    ss   SSSS
2    dd   DDDD

已选择8行。

SQL> select s.*,p.* from
  2  (
  3  select a.*,b.mc MC1, c.mc MC2
  4  from ts a,tdm b, tdm c
  5  where  A.dm1 = B.DM(+) AND B.type(+)='1'
  6  and  A.dm2 = c.DM(+) AND c.type(+)='2'
  7  ) s full outer join
  8  (
  9  select * from tp
10  )  p
11  on s.pid=p.id
12  /

ID   PID  DM1  DM2  MC1        MC2        ID   CON
---- ---- ---- ---- ---------- ---------- ---- ----
3    1    a    ss   AA         SSSS       1    a
1    1    a    aa   AA         AAAA       1    a
2    2    b         BB                    2    b
5    3    c         CC                    3    c
4    3    a    ss   AA         SSSS       3    c
9         a         AA
10   9
7    7    c    ee   CC
                                          6    f
                                          2    b

已选择10行。

SQL> select s.*,p.* from
  2  (
  3  select a.*,b.mc MC1, c.mc MC2 from ts a,tdm b, tdm c
  4  where  A.dm1 = B.DM(+) AND B.type(+)='1'
  5  and  A.dm2 = c.DM(+) AND c.type(+)='2'
  6  ) s left outer join
  7  (
  8  select * from tp
  9  )  p
10  on s.pid=p.id
11  union
12  select s.*,p.* from
13  (
14  select a.*,b.mc MC1, c.mc MC2 from ts a,tdm b, tdm c
15  where  A.dm1 = B.DM(+) AND B.type(+)='1'
16  and  A.dm2 = c.DM(+) AND c.type(+)='2'
17  ) s right outer join
18  (
19  select * from tp
20  )  p
21  on s.pid=p.id
22  /

ID   PID  DM1  DM2  MC1        MC2        ID   CON
---- ---- ---- ---- ---------- ---------- ---- ----
1    1    a    aa   AA         AAAA       1    a
10   9
2    2    b         BB                    2    b
3    1    a    ss   AA         SSSS       1    a
4    3    a    ss   AA         SSSS       3    c
5    3    c         CC                    3    c
7    7    c    ee   CC
9         a         AA
                                          6    f

已选择9行。
。。。。。。。。。。。。。。。
[/php]

可见在这里出现了FULL OUTER JOIN <> LEFT OUTER JOIN UNION RIGHT OUTER JOIN的现象,而按常理这两个应该是相等的
即FULL OUTER JOIN = LEFT OUTER JOIN UNION RIGHT OUTER JOIN并且还有
LEFT OUTER JOIN UNION ALL RIGHT OUTER JOIN - INNER JOIN = FULL OUTER JOIN
如果表TS与表TDM只外连接一次,那么不会出现这个现象。

另外还发现一个有趣的怪现象,对表TP做一个简单的变换(并不影响其结果集的记录数),做一次full outer join
然后加上一个条件限制(也不影响其结果集的记录数),再做一次full outer join
两次的结果居然不一样,而且还都是不对的

[php]
SQL> select s.*,p.* from
  2  (
  3  select a.*,b.mc MC1, c.mc MC2
  4  from ts a,tdm b, tdm c
  5  where  A.dm1 = B.DM(+) AND B.type(+)='1'
  6  and  A.dm2 = c.DM(+) AND c.type(+)='2'
  7  ) s full outer join
  8  (
  9  select * from (select tp.*,rank()over(order by id) rn from tp) --where rn<10
10  )  p
11  on s.pid=p.id
12  /

ID   PID  DM1  DM2  MC1        MC2        ID   CON          RN
---- ---- ---- ---- ---------- ---------- ---- ---- ----------
3    1    a    ss   AA         SSSS       1    a             1
1    1    a    aa   AA         AAAA       1    a             1
2    2    b         BB                    2    b             2
5    3    c         CC                    3    c             3
4    3    a    ss   AA         SSSS       3    c             3
9         a         AA
10   9
7    7    c    ee   CC
                                          6    f
                                          2    b

已选择10行。

这个和刚才的测试6效果一样,最后一条记录是多余的

SQL> select s.*,p.* from
  2  (
  3  select a.*,b.mc MC1, c.mc MC2
  4  from ts a,tdm b, tdm c
  5  where  A.dm1 = B.DM(+) AND B.type(+)='1'
  6  and  A.dm2 = c.DM(+) AND c.type(+)='2'
  7  ) s full outer join
  8  (
  9  select * from (select tp.*,rank()over(order by id) rn from tp) where rn<10
10  )  p
11  on s.pid=p.id
12  /

ID   PID  DM1  DM2  MC1        MC2        ID   CON          RN
---- ---- ---- ---- ---------- ---------- ---- ---- ----------
3    1    a    ss   AA         SSSS       1    a             1
1    1    a    aa   AA         AAAA       1    a             1
2    2    b         BB                    2    b             2
5    3    c         CC                    3    c             3
4    3    a    ss   AA         SSSS       3    c             3
9         a         AA
10   9
7    7    c    ee   CC

已选择8行。

这个结果跟左连接没啥区别-V-

SQL> select s.*,p.* from
  2  (
  3  select a.*,b.mc MC1, c.mc MC2 from ts a,tdm b, tdm c
  4  where  A.dm1 = B.DM(+) AND B.type(+)='1'
  5  and  A.dm2 = c.DM(+) AND c.type(+)='2'
  6  ) s left outer join
  7  (
  8  select * from tp
  9  )  p
10  on s.pid=p.id
11  /

ID   PID  DM1  DM2  MC1        MC2        ID   CON
---- ---- ---- ---- ---------- ---------- ---- ----
3    1    a    ss   AA         SSSS       1    a
1    1    a    aa   AA         AAAA       1    a
2    2    b         BB                    2    b
5    3    c         CC                    3    c
4    3    a    ss   AA         SSSS       3    c
9         a         AA
10   9
7    7    c    ee   CC

已选择8行。

。。。。。。。。。。。。。。。。。。。
[/php]
招聘 : 系统分析师
论坛徽章:
483
马上有钱
日期:2014-02-19 11:55:14itpub13周年纪念徽章
日期:2014-09-29 01:14:14itpub13周年纪念徽章
日期:2014-10-08 15:15:25itpub13周年纪念徽章
日期:2014-10-08 15:15:25马上有对象
日期:2014-10-12 11:58:40马上有车
日期:2014-11-16 17:11:29慢羊羊
日期:2015-02-09 17:04:38沸羊羊
日期:2015-03-04 14:43:432015年新春福章
日期:2015-03-06 11:57:31ITPUB年度最佳版主
日期:2015-03-18 15:48:48
 楼主| 发表于 2005-11-22 13:41 | 显示全部楼层
Bug2: FULL OUTER JOIN = LEFT OUTER JOIN

奇怪的是,如果将
select a.*,b.mc MC1, c.mc MC2
from ts a,tdm b, tdm c
where  A.dm1 = B.DM(+) AND B.type(+)='1'
and  A.dm2 = c.DM(+) AND c.type(+)='2'
创建为视图
create view vts as select a.*,b.mc MC1, c.mc MC2
from ts a,tdm b, tdm c
where  A.dm1 = B.DM(+) AND B.type(+)='1'
and  A.dm2 = c.DM(+) AND c.type(+)='2'
/
则下面的查询结果就是正确的
[php]
SQL> select s.*,p.* from
  2  vts s full outer join
  3  (select * from (select tp.*,rank()over(order by id) rn from tp) --where rn<10
  4  )  p
  5  on s.pid=p.id
  6  /

ID   PID  DM1  DM2  MC1        MC2        ID   CON          RN
---- ---- ---- ---- ---------- ---------- ---- ---- ----------
3    1    a    ss   AA         SSSS       1    a             1
1    1    a    aa   AA         AAAA       1    a             1
2    2    b         BB                    2    b             2
5    3    c         CC                    3    c             3
4    3    a    ss   AA         SSSS       3    c             3
9         a         AA
10   9
7    7    c    ee   CC
                                          6    f

已选择9行。
。。。。。。。。。。。。。。。。
[/php]

但是加上一个无关紧要的条件限制后,查询结果就不正确了,其结果也相当于是左连接
[php]
SQL> select s.*,p.* from
  2  vts s full outer join
  3  (select * from (select tp.*,rank()over(order by id) rn from tp) where rn<10
  4  )  p
  5  on s.pid=p.id
  6  /

ID   PID  DM1  DM2  MC1        MC2        ID   CON          RN
---- ---- ---- ---- ---------- ---------- ---- ---- ----------
3    1    a    ss   AA         SSSS       1    a             1
1    1    a    aa   AA         AAAA       1    a             1
2    2    b         BB                    2    b             2
5    3    c         CC                    3    c             3
4    3    a    ss   AA         SSSS       3    c             3
9         a         AA
10   9
7    7    c    ee   CC

已选择8行。
。。。。。。。。。。。。。。。
[/php]

代码里用的rank而没用rownum,因为我这里一用rownum就出现Ora-03113

若将select * from (select tp.*,rank()over(order by id) rn from tp) where rn<10也创建为一个视图
create view vtp as select * from (select tp.*,rank()over(order by id) rn from tp) where rn<10
则Full Outer Join的查询结果是正确的
[php]
SQL> select s.*,p.* from
  2  vts s full outer join
  3  vtp p
  4  on s.pid=p.id
  5  /

ID   PID  DM1  DM2  MC1        MC2        ID   CON          RN
---- ---- ---- ---- ---------- ---------- ---- ---- ----------
3    1    a    ss   AA         SSSS       1    a             1
1    1    a    aa   AA         AAAA       1    a             1
2    2    b         BB                    2    b             2
5    3    c         CC                    3    c             3
4    3    a    ss   AA         SSSS       3    c             3
9         a         AA
10   9
7    7    c    ee   CC
                                          6    f             4

已选择9行。
........................。。。。。
[/php]

使用道具 举报

回复
论坛徽章:
0
发表于 2005-11-22 16:59 | 显示全部楼层
这种情况我也碰到过,具体原因也不知道。象full join一般我都不敢用了

使用道具 举报

回复
论坛徽章:
0
发表于 2005-11-22 17:04 | 显示全部楼层
。。。。。。。

使用道具 举报

回复
招聘 : 系统分析师
论坛徽章:
483
马上有钱
日期:2014-02-19 11:55:14itpub13周年纪念徽章
日期:2014-09-29 01:14:14itpub13周年纪念徽章
日期:2014-10-08 15:15:25itpub13周年纪念徽章
日期:2014-10-08 15:15:25马上有对象
日期:2014-10-12 11:58:40马上有车
日期:2014-11-16 17:11:29慢羊羊
日期:2015-02-09 17:04:38沸羊羊
日期:2015-03-04 14:43:432015年新春福章
日期:2015-03-06 11:57:31ITPUB年度最佳版主
日期:2015-03-18 15:48:48
 楼主| 发表于 2005-11-24 09:18 | 显示全部楼层
顶起

使用道具 举报

回复
论坛徽章:
90
生肖徽章:蛇
日期:2006-09-07 17:09:082011新春纪念徽章
日期:2011-01-25 15:42:332011新春纪念徽章
日期:2011-01-25 15:42:562011新春纪念徽章
日期:2011-02-18 11:43:34现任管理团队成员
日期:2011-05-07 01:45:082012新春纪念徽章
日期:2012-01-04 11:50:442012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:09
发表于 2005-11-24 11:30 | 显示全部楼层
在10g (10.1.0.3.0) 下测试, 基本和楼主的描述相同, 不过有一个不同, 如下
SQL> select s.*, p.*
  2    from vw_ts_tdm s
  3    full outer join (
  4    select * from (select tp.*,rank()over(order by id) rn from tp) --where rn<10
  5    ) p on s.pid = p.id
  6  /

ID   PID  DM1  DM2  MC1        MC2        ID   CON          RN
---- ---- ---- ---- ---------- ---------- ---- ---- ----------
1    1    a    aa   AA         AAAA       1    a             1
3    1    a    ss   AA         SSSS       1    a             1
4    3    a    ss   AA         SSSS       3    c             3
10   9                                             
5    3    c         CC                    3    c             3
2    2    b         BB                    2    b             2
9         a         AA                              
7    7    c    ee   CC                              
                                          6    f   
                                          2    b   

10 rows selected
即即使把
select a.*,b.mc MC1, c.mc MC2
from ts a,tdm b, tdm c
where A.dm1 = B.DM(+) AND B.type(+)='1'
and A.dm2 = c.DM(+) AND c.type(+)='2'
做成视图, 得到的结果仍然是十条, 而不是像楼主那样得到9条数据

使用道具 举报

回复
论坛徽章:
90
生肖徽章:蛇
日期:2006-09-07 17:09:082011新春纪念徽章
日期:2011-01-25 15:42:332011新春纪念徽章
日期:2011-01-25 15:42:562011新春纪念徽章
日期:2011-02-18 11:43:34现任管理团队成员
日期:2011-05-07 01:45:082012新春纪念徽章
日期:2012-01-04 11:50:442012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:09
发表于 2005-11-24 11:38 | 显示全部楼层
另外, 如果把
select a.*,b.mc MC1, c.mc MC2
from ts a,tdm b, tdm c
where A.dm1 = B.DM(+) AND B.type(+)='1'
and A.dm2 = c.DM(+) AND c.type(+)='2'
改为
select a.*, b.mc MC1, c.mc MC2
  from ts a
  left outer join tdm b on A.dm1 = B.DM and b.type = '1'
  left outer join tdm c on A.dm2 = c.DM and c.type = '1'
则楼主所列的问题全部消失,
难道是这两个sql不等价, 还是sql中(+)在解析的时候出了问题, 没有想通, 有明白的朋友出来说说

使用道具 举报

回复
招聘 : 系统分析师
论坛徽章:
483
马上有钱
日期:2014-02-19 11:55:14itpub13周年纪念徽章
日期:2014-09-29 01:14:14itpub13周年纪念徽章
日期:2014-10-08 15:15:25itpub13周年纪念徽章
日期:2014-10-08 15:15:25马上有对象
日期:2014-10-12 11:58:40马上有车
日期:2014-11-16 17:11:29慢羊羊
日期:2015-02-09 17:04:38沸羊羊
日期:2015-03-04 14:43:432015年新春福章
日期:2015-03-06 11:57:31ITPUB年度最佳版主
日期:2015-03-18 15:48:48
 楼主| 发表于 2005-11-24 16:02 | 显示全部楼层
谢谢jackywood关注,这两个SQL确实不等价,后一个应该为“c.type = '2'”,不过这并不影响以下试验的结果集的记录数
难道是不同版本的标记((+)和left out join等)混合使用造成的问题?
使用
select s.*,p.* from
(
select a.*, b.mc MC1, c.mc MC2
from ts a
left outer join tdm b on A.dm1 = B.DM and b.type = '1'
left outer join tdm c on A.dm2 = c.DM and c.type = '2'
) s full outer join
(
select * from tp
) p
on s.pid=p.id
去测试,结果集就正确了

但是2楼中所列的问题依然存在
[php]
SQL> create or replace view vts as select a.*, b.mc MC1, c.mc MC2
  2  from ts a
  3  left outer join tdm b on A.dm1 = B.DM and b.type = '1'
  4  left outer join tdm c on A.dm2 = c.DM and c.type = '2'
  5  /

视图已建立。

SQL> select s.*,p.* from
  2  vts s full outer join
  3  (select * from (select tp.*,rank()over(order by id) rn from tp) --where rn<10
  4  )  p
  5  on s.pid=p.id
  6  /

ID   PID  DM1  DM2  MC1        MC2        ID   CON          RN
---- ---- ---- ---- ---------- ---------- ---- ---- ----------
3    1    a    ss   AA         SSSS       1    a             1
1    1    a    aa   AA         AAAA       1    a             1
2    2    b         BB                    2    b             2
5    3    c         CC                    3    c             3
4    3    a    ss   AA         SSSS       3    c             3
9         a         AA
10   9
7    7    c    ee   CC
                                          6    f

已选择9行。

SQL> select s.*,p.* from
  2  vts s full outer join
  3  (select * from (select tp.*,rank()over(order by id) rn from tp) where rn<10
  4  )  p
  5  on s.pid=p.id
  6  /

ID   PID  DM1  DM2  MC1        MC2        ID   CON          RN
---- ---- ---- ---- ---------- ---------- ---- ---- ----------
3    1    a    ss   AA         SSSS       1    a             1
1    1    a    aa   AA         AAAA       1    a             1
2    2    b         BB                    2    b             2
5    3    c         CC                    3    c             3
4    3    a    ss   AA         SSSS       3    c             3
9         a         AA
10   9
7    7    c    ee   CC

已选择8行。


但把视图换成sql又是对的了-_-
SQL> select s.*,p.* from
  2  (
  3  select a.*, b.mc MC1, c.mc MC2
  4  from ts a
  5  left outer join tdm b on A.dm1 = B.DM and b.type = '1'
  6  left outer join tdm c on A.dm2 = c.DM and c.type = '2'
  7  ) s full outer join
  8   (select * from (select tp.*,rank()over(order by id) rn from tp) where rn<1
0
  9   )  p
10   on s.pid=p.id
11  /

ID   PID  DM1  DM2  MC1        MC2        ID   CON          RN
---- ---- ---- ---- ---------- ---------- ---- ---- ----------
3    1    a    ss   AA         SSSS       1    a             1
1    1    a    aa   AA         AAAA       1    a             1
2    2    b         BB                    2    b             2
5    3    c         CC                    3    c             3
4    3    a    ss   AA         SSSS       3    c             3
9         a         AA
10   9
7    7    c    ee   CC
                                          6    f             4

已选择9行。

...........................
[/php]

使用道具 举报

回复
论坛徽章:
90
生肖徽章:蛇
日期:2006-09-07 17:09:082011新春纪念徽章
日期:2011-01-25 15:42:332011新春纪念徽章
日期:2011-01-25 15:42:562011新春纪念徽章
日期:2011-02-18 11:43:34现任管理团队成员
日期:2011-05-07 01:45:082012新春纪念徽章
日期:2012-01-04 11:50:442012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:09
发表于 2005-11-26 00:58 | 显示全部楼层
可能是9i中的bug吧, 我前面的测试环境是10g (10.1.0.3.0), 二楼的错误也已经消失了
今天在9i中重新做了测试, 确实如楼主所说的, 问题根本所在, 不是很清楚, 也没有想通为什么

使用道具 举报

回复
论坛徽章:
59
马上加薪
日期:2014-02-19 11:55:142012新春纪念徽章
日期:2012-02-13 15:11:522012新春纪念徽章
日期:2012-01-04 11:49:54ITPUB十周年纪念徽章
日期:2011-11-01 16:19:41灰彻蛋
日期:2011-10-28 14:15:35管理团队成员
日期:2011-05-07 01:45:082011新春纪念徽章
日期:2011-02-18 11:43:332011新春纪念徽章
日期:2011-01-25 15:42:562011新春纪念徽章
日期:2011-01-25 15:42:332011新春纪念徽章
日期:2011-01-25 15:42:15
发表于 2005-11-26 02:04 | 显示全部楼层
恩, 很有钻研精神啊, 仰慕一哈子.
偶怀疑ORACLE OUTER JOIN 和 ANSI OUTER JOIN有所不同.

用UNION模拟FULL  OUTER JOIN 是ORACLE自己的做法, 用FULL OUTER JOIN 做则是数据库的工业标准规定啦. 二者在处理方式上也可能会有所不同地.

BTW, 如果用的例子简单典型一些, 就更容易发现问题啦.

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表