我这里要在很多表里检索数据,其中一个主表,多个从表,用外键关联,且主表对从表都是一对多的关系,我现在要这样检索数据,
select a.*,b.*,c.*,d.*,e.*
from table1 a,table2 b,table3 c,table4 d,table5 e
where a.id = b.id(*) and
a.id = c.id(*) and
a.id = d.id(*) and
a.id = e.id(*) and
a.id = 111
因为a表和其他表是一对多的关系,所以现在检索出的数据是b,c,d,e表中多个记录数的乘积,数据量太大了,速度太慢,请问哪位高手给解决一下,让检索出的数据的记录不用重复的吗?
谢谢大家了!
select a.*,b.*,c.*,d.*,e.*
from table1 a,table2 b,table3 c,table4 d,table5 e
where a.id = b.id(*) and
a.id = c.id(*) and
a.id = d.id(*) and
a.id = e.id(*) and
a.id = 111
因为a表和其他表是一对多的关系,所以现在检索出的数据是b,c,d,e表中多个记录数的乘积,数据量太大了,速度太慢,请问哪位高手给解决一下,让检索出的数据的记录不用重复的吗?
谢谢大家了!
from table1 a,table2 b,table3 c,table4 d,table5 e
where a.id = b.id(+) and
a.id = c.id(+) and
a.id = d.id(+) and
a.id = e.id(+) and
a.id = 111
from table1 a left outer join table2 b on(a.id=b.id)
left outer join table3 c on(a.id=c.id)
left outer join table4 d on(a.id=d.id)
left outer join table5 e on(a.id=e.id)
where a.id = 111
from table2 b
where b.id = 111
union all
select c.*
from table3 c
where c.id = 111
union all
select d.*
from table4 d
where d.id = 111
union all
select e.*
from table5 e
where e.id = 111
那么检索出的数据的记录数就是 2*3*4*5 = 120条,
当数据量大的时候,或者表更多的时候,数据量就特大了,还有冗余
我现在想的是能不能就出来 5 条,就像下面这样:
e.id d.id c.id b.id a.id
e1 d1 c1 b1 a1
e2 d2 c2 b2
e3 c3 b3
e4 c4
e5不知道怎么样做到这样。
这样的b c d e 表的结果会算笛卡尔集,现在是4个表,我用的表是30来个,这样下来数据量很大,
所有的数据都重复了许多次,有用的数据就那么几条。
e.id d.id c.id b.id a.id
1 e1 d1 c1 b1 a1
2 e2 d2 c2 b2
3 e3 c3 b3
4 e4 c4
5 e5
查出结构是个二维表 2,3,4,5 行为空的地方显示什么啊?
我觉得你可以写程序去构造这样的表,连表 b,c,d,e 都没有关系,我觉得不太靠谱
(
select a.id,b.*
from table1 a,table2 b
where a.id = b.id(+)
) T1,
(
select a.id,c.* from table1 a,table3 c
where a.id = c.id(+)
)T2,
(
select a.id,d.* from table1 a,table4 d
where a.id = d.id(+)
)T3,
(
select a.id,e.* from table1 a,table5 e
where a.id = e.id(+)
)T4
where T1.ID = T2.ID
AND T2.ID = T3.ID
AND T3.ID = T4.ID
(
select a.id row_number() over(partition by id order by id) secondid
from table1 a
)T0,
(
select b.id row_number() over(partition by id order by id) secondid
from table2 B
)T1,
(
select c.id,row_number() over(partition by id order by id) secondid
from table3 C
)T2,
(
select b.id,row_number() over(partition by id order by id) secondid
from table4 D
)T3,
(
select c.id,row_number() over(partition by id order by id) secondid
from table5 E
)T4
where
T0.id = T1.ID AND T0.secondid = T1. secondid
T0.id = T2.ID AND T0.secondid = T2. secondid
T0.id = T3.ID AND T0.secondid = T3. secondid
T0.id = T4.ID AND T0.secondid = T4. secondid不知道是否可行,我也在学习,呵呵
就拿3个表来说吧
A表数据
ID
---
a1
B表数据
A_ID B_ID
-----------
a1 b1
a1 b2
C表数据
A_ID C_ID
-----------
a1 c1
a1 c2
a1 c3
按道理来说,得到的应该是下面的数据
A_ID B_ID C_ID
-------------------
a1 b1 c1
a1 b1 c2
a1 b1 c3
a1 b2 c1
a1 b2 c2
a1 b2 c3
那你现在要得到那几条数据??依什么条件去判断???
A_ID B_ID C_ID
-------------------
a1 b1 c1
null b2 c2
null null c3也就是得到的数据不用做笛卡尔积,因为那样的话如果表中关键数据太多的时候,数据量太大,得出上面这个结果已经能达到我的要求了。
a1 b1 c2
null b2 c1
null null c3
这中间的条件是什么呢
a1 b1 c2
null b2 c1
null null c3
也可以,只要能出来就可以,我现在想要的就是并集,排序和记录中的关系可以不考虑
(id varchar2(2));create table b
(id varchar2(2));create table c
(id varchar2(2));create table d
(id varchar2(2));create table e
(id varchar2(2));insert into a values('a1');insert into b values('b1');
insert into b values('b2');
insert into b values('b3');insert into c values('c1');
insert into c values('c2');
insert into c values('c3');
insert into c values('c3');insert into d values('d1');
insert into d values('d2');insert into e values('e1');
insert into e values('e2');
insert into e values('e3');
insert into e values('e4');
insert into e values('e5');
select a.id,b.id,c.id,d.id,e.id from
(select id,rownum rn from e) e left outer join (select id,rownum rn from a) a on(e.rn=a.rn)
left outer join (select id,rownum rn from b) b on(e.rn=b.rn)
left outer join (select id,rownum rn from c) c on(e.rn=c.rn)
left outer join (select id,rownum rn from d) d on(e.rn=d.rn)
这么多人回答...晕倒...之前问错地方了..⊙﹏⊙b汗...给UP...
为什么要这样分一个主表,四个从表?
是不是有必要这样建表?