两个表,a,b,
a有p_id,name,sort,type
1 2 2 2
1 3 3 3
b有p_id,name
1 4
1 5
想查出p_id=1的a.name和a.type 和b.name
要求:不可有冗余数据 a.p_id = b.p_id and p_id=1只访问一次数据库
order by a.sort
2 2 4
3 3 5
要这种效果。
a有p_id,name,sort,type
1 2 2 2
1 3 3 3
b有p_id,name
1 4
1 5
想查出p_id=1的a.name和a.type 和b.name
要求:不可有冗余数据 a.p_id = b.p_id and p_id=1只访问一次数据库
order by a.sort
2 2 4
3 3 5
要这种效果。
from a,b
where a.p_id=b.p_id
and a.p_id = 1
order by a.sort
只能得到:
2 2 4
2 2 5
3 3 4
3 3 5
(select rownum rn,b.* from b) t2
where p_id=1 and a.rn=t2.rn
insert into a values( 1,'3','3','3');select * from a;drop table b;create table b(p_id number(10),name varchar2(10));insert into b values( 1,'4');
insert into b values( 1,'5');select * from b;
select a.sort, a.type, b.name
from (select p_id, sort, type, rownum row1 from a) a,
(select p_id, name, rownum row1 from b) b
where a.p_id = b.p_id
and a.row1 = b.row1
order by a.sort