如现有两个表A、B
A按NO已排好序的记录如下:
AID PID ANO
-----------------
A1 1 1
A2 1 1
A3 1 2
A4 1 2
A5 1 3
A6 1 3B按NO已排好序的记录如下
BID PID BNO
------------------
B1 1 1
B2 1 1
B3 1 1
B4 1 2
B5 1 2
B6 1 2现在两个表根据PID字段进行连接,但是连接后只会保持B的排序,能不能A、B两个表的排序都保持,即连接后为以下效果:AID PID ANO BID PID BNO
--------------------------------------
A1 1 1 B1 1 1
A2 1 1 B2 1 1
A3 1 2 B3 1 1
A4 1 2 B4 1 2
A5 1 3 B5 1 2
A6 1 3 B6 1 2请解答,谢谢
A按NO已排好序的记录如下:
AID PID ANO
-----------------
A1 1 1
A2 1 1
A3 1 2
A4 1 2
A5 1 3
A6 1 3B按NO已排好序的记录如下
BID PID BNO
------------------
B1 1 1
B2 1 1
B3 1 1
B4 1 2
B5 1 2
B6 1 2现在两个表根据PID字段进行连接,但是连接后只会保持B的排序,能不能A、B两个表的排序都保持,即连接后为以下效果:AID PID ANO BID PID BNO
--------------------------------------
A1 1 1 B1 1 1
A2 1 1 B2 1 1
A3 1 2 B3 1 1
A4 1 2 B4 1 2
A5 1 3 B5 1 2
A6 1 3 B6 1 2请解答,谢谢
from A,B
where A.pid=B.id(+)
order by pid,Aid,Bid
from (
SELECT a.*,rownum r from a
) a1,
(
SELECT b.*,rownum r from b
) b1
where a1.r = b1.r
create table t1 (AID varchar2(2), PID varchar2(2), ANO varchar2(2));
-----------------
insert into t1 values('A1', '1', '1');
insert into t1 values('A2', '1', '1');
insert into t1 values('A3', '1', '2');
insert into t1 values('A4', '1', '2');
insert into t1 values('A5', '1', '3');
insert into t1 values('A6', '1', '3'); create table t2 (BID varchar2(2), PID varchar2(2), BNO varchar2(2));
insert into t2 values('B1', '1', '1');
insert into t2 values('B2', '1', '1');
insert into t2 values('B3', '1', '1');
insert into t2 values('B4', '1', '2');
insert into t2 values('B5', '1', '2');
insert into t2 values('B6', '1', '2'); select y.aid, y.pid, y.ano, t2.*
from (select x.*, rownum ord
from (select a.* from t1 a order by a.aid, a.pid, a.ano) x) y,
t2
where y.pid = t2.pid
order by y.ord, t2.bid, t2.pid, t2.bno
A表排序好的记录如下:
AID PID ANO
----------------------
A1 1 1
A2 1 1
A3 1 1
A4 1 2
A5 1 2
A6 1 2
A7 1 3
B表排序好的记录如下
BID PID BNO
---------------------
B1 1 1
B2 1 2
现在需要按PID连接后达到下面的效果有没有办法
AID PID ANO BID PID BNO
--------------------------------------------
A1 1 1 B1 1 1
A2 2 1 B2 2 1
A3 3 1 B3 3 1
A4 4 1 B7 4 1
A5 5 1 B8 5 1
A6 6 1 B9 6 1
A7 7 2 B13 7 1
A8 8 2 B4 8 2
A9 9 2 B5 9 2
A10 10 2 B6 10 2
A11 11 2 B10 11 2
A12 12 2 B11 12 2
A13 13 3 B12 13 2
A14 14 3 B14 14 2如果按照先后排序会得到下面的效果,不是我想要的
AID PID ANO BID PID BNO
----------------------------------------------
A1 1 1 B1 1 1
A2 2 1 B2 2 1
A3 3 1 B3 3 1
A4 4 1 B4 4 2
A5 5 1 B5 5 2
A6 6 1 B6 6 2
A7 7 2 B7 7 1
A8 8 2 B8 8 1
A9 9 2 B9 9 1
A10 10 2 B10 10 2
A11 11 2 B11 11 2
A12 12 2 B12 12 2
A13 13 3 B13 13 1
A14 14 3 B14 14 2如果不行,我只有另外想办法了
运行出来的结果也是不对的!
PID,会根据那个左链接后,有36行的数据!
from (select x.*, rownum ord
from (select a.* from t1 a order by a.aid, a.pid, a.ano) x) y,
(select x.*, rownum ord
from (select a.* from t2 a order by a.bid, a.pid, a.bno) x) z
where y.ord = z.ord
order by y.ord
这个总是你想要的了吧
create table t1 (AID varchar2(2), PID varchar2(2), ANO varchar2(2));
-----------------
insert into t1 values('A1', '1', '1');
insert into t1 values('A2', '1', '1');
insert into t1 values('A3', '1', '2');
insert into t1 values('A4', '1', '2');
insert into t1 values('A5', '1', '3');
insert into t1 values('A6', '1', '3'); create table t2 (BID varchar2(2), PID varchar2(2), BNO varchar2(2));
insert into t2 values('B1', '1', '1');
insert into t2 values('B2', '1', '1');
insert into t2 values('B3', '1', '1');
insert into t2 values('B4', '1', '2');
insert into t2 values('B5', '1', '2');
insert into t2 values('B6', '1', '2'); select y.aid, y.pid, y.ano, z.bid, z.pid, z.bno
from (select x.*, rownum ord
from (select a.* from t1 a order by a.aid, a.pid, a.ano) x) y,
(select x.*, rownum ord
from (select a.* from t2 a order by a.bid, a.pid, a.bno) x) z
where y.ord = z.ord
order by y.ord
2 A2 1 1 B2 1 1
3 A3 1 2 B3 1 1
4 A4 1 2 B4 1 2
5 A5 1 3 B5 1 2
6 A6 1 3 B6 1 2
from A,B
where A.pid=B.pid and substr(Aid,2,1)=substr(Bid,2,1)
order by A.pid,Aid,Bid
from (select x.*, rownum ord
from (select a.* from t1 a order by a.aid, a.pid, a.ano) x) y,
(select x.*, rownum ord
from (select a.* from t2 a order by a.bid, a.pid, a.bno) x) z
where y.ord = z.ord(+)
order by y.ord
1 A1 1 1 B1 1 1
2 A2 1 1 B2 1 2
3 A3 1 2
4 A4 1 2
5 A5 1 3
6 A6 1 3