table1
product_no product_id prod_addrtable2
product_id obj_idtable3
connect_id connect_codetable4
phone_no cust_idtable5
cust_id cust_name条件
where table1.product_id=table2.product_id
table2.obj_id=table3.connect_id
table4.cust_id=table5.cust_id
table4.phone_no=table1.product.no结果
table1.prod_addr table1.product_no cust_name table3.connect_code
_________________ __________________ ___________ ____________________我写的语句如下:select a.prod_addr,a.product_no,c.connect_code,e.cust_name
where
a.product_id=b.product_id
and b.obj_id=c.connect_id
and d.cust_id=e.cust_id
and d.phone_no=a.product_no
from table1 a,table2 b,table3 c,table4 d, table5 e 得到结果出现N条重复的记录
请问我写的这个查询问题在哪?
象是出现笛卡儿乘积现象。
如何改?谢谢了!
急等!
product_no product_id prod_addrtable2
product_id obj_idtable3
connect_id connect_codetable4
phone_no cust_idtable5
cust_id cust_name条件
where table1.product_id=table2.product_id
table2.obj_id=table3.connect_id
table4.cust_id=table5.cust_id
table4.phone_no=table1.product.no结果
table1.prod_addr table1.product_no cust_name table3.connect_code
_________________ __________________ ___________ ____________________我写的语句如下:select a.prod_addr,a.product_no,c.connect_code,e.cust_name
where
a.product_id=b.product_id
and b.obj_id=c.connect_id
and d.cust_id=e.cust_id
and d.phone_no=a.product_no
from table1 a,table2 b,table3 c,table4 d, table5 e 得到结果出现N条重复的记录
请问我写的这个查询问题在哪?
象是出现笛卡儿乘积现象。
如何改?谢谢了!
急等!
select distinct a.prod_addr,a.product_no,c.connect_code,e.cust_name
from table1 a,table2 b,table3 c,table4 d, table5 e
where
a.product_id=b.product_id
and b.obj_id=c.connect_id
and a.product_no=d.phone_no
and d.cust_id=e.cust_id
1条记录就重复了几十万遍。实在无法想象!
谢谢各位,继续顶!
我写的语句是这样的!
select distinct a.prod_addr,a.product_no,c.connect_code,e.cust_name
from table1 a,table2 b,table3 c,table4 d, table5 e
where
a.product_id=b.product_id
and b.obj_id=c.connect_id
and a.product_no=d.phone_no
and d.cust_id=e.cust_id
select distinct a.prod_addr,a.product_no,
b.connect_code,
c.cust_name
from table1 a,
(select distinct a.product_id,b.connect_code from table2 a inner join table3 b on a.obj_id=b.connect_id ) b,
(select distinct a.phone_no,b.cust_name from table4 a inner join table5 b on a.cust_id=b.cust_id ) c
where
a.product_id=b.product_id
and a.product_no=c.phone_no