测试用例如下:
create table test4 (
id varchar2(10)
);
create table test5 (
id varchar2(10)
);
insert into TEST4 (ID)
values ('3');
insert into TEST4 (ID)
values ('4');
insert into TEST4 (ID)
values ('5');
insert into TEST4 (ID)
values ('1');
insert into TEST4 (ID)
values ('2');
insert into TEST5 (ID)
values ('1');
insert into TEST5 (ID)
values ('2');
commit;SQL> select * from test4;ID
----------
3
4
5
1
25 rows selected
SQL> select * from test5;ID
----------
1
22 rows selected
我想用test4 右连 test5,得到的结果集仍旧按照原来test4的排序,我用的sql如下
SQL> SELECT T.ID, A.ID FROM TEST4 T, TEST5 A WHERE T.ID = A.ID(+);ID ID
---------- ----------
1 1
2 2
3
4
55 rows selected发现结果集的顺序已经不是原来的,大家谁能告诉我应该怎么写,使得右连以后的结果集仍旧按照test4原来的排序,谢谢了!!
create table test4 (
id varchar2(10)
);
create table test5 (
id varchar2(10)
);
insert into TEST4 (ID)
values ('3');
insert into TEST4 (ID)
values ('4');
insert into TEST4 (ID)
values ('5');
insert into TEST4 (ID)
values ('1');
insert into TEST4 (ID)
values ('2');
insert into TEST5 (ID)
values ('1');
insert into TEST5 (ID)
values ('2');
commit;SQL> select * from test4;ID
----------
3
4
5
1
25 rows selected
SQL> select * from test5;ID
----------
1
22 rows selected
我想用test4 右连 test5,得到的结果集仍旧按照原来test4的排序,我用的sql如下
SQL> SELECT T.ID, A.ID FROM TEST4 T, TEST5 A WHERE T.ID = A.ID(+);ID ID
---------- ----------
1 1
2 2
3
4
55 rows selected发现结果集的顺序已经不是原来的,大家谁能告诉我应该怎么写,使得右连以后的结果集仍旧按照test4原来的排序,谢谢了!!
2 test5.id
3 from (select test4.*,rownum rn
4 from (select 3 as id from dual
5 union all
6 select 4 as id from dual
7 union all
8 select 5 as id from dual
9 union all
10 select 1 as id from dual
11 union all
12 select 2 as id from dual
13 )test4
14 )z4,
15 (select 1 as id from dual
16 union all
17 select 2 as id from dual
18 )test5
19 where z4.id = test5.id(+)
20 order by rn; ID ID
---------- ----------
3
4
5
1 1
2 2