比如 table1:id,field2,field3; table2:id,field3,field4; table3:id,field5,field6; .....table2,table3的id都是table1的外键。现在要把table1.id,table1.field2,table1.field3,table2.field3,table2.field4,table3.field5,table3.field6...取出来。sql怎么写好呢? select table1.id.... from table1 left join table2 on table1 .id = table2.id left join table3 on table1.id = table3.id ...????不对吧?
可以啊,还可以这样的 select a.id.... from table1 a,table2 b,table 3 where a.id = b.id(+) and a.id = c.id(+)
呵呵,表多少沒有關系 主要是編寫的思路能不能理清 from 後數據量小的寫在最後,依次往from靠近
建议使用临时表,归集数据后,使用update更新属性
多表連接,同時要考虛到較率問題,,如果九個表都是數據量很大的話,那么像7樓的寫法可能會降低效率,建義用一條SQL寫出來,,寫法是,兩兩做關聯(如果帶有條件的先運行帶條件的表找出數據后給個別名再關聯) 注:先把有帶條件的數據找出來,盡量減小搜索范圍select ...from ( select ... from (select 要打的字段 from a,b where a.id =b.id) ab,c )abc ,d where ....... . . . .
table2:id,field3,field4;
table3:id,field5,field6;
.....table2,table3的id都是table1的外键。现在要把table1.id,table1.field2,table1.field3,table2.field3,table2.field4,table3.field5,table3.field6...取出来。sql怎么写好呢?
select table1.id.... from table1 left join table2 on table1 .id = table2.id left join table3 on table1.id = table3.id ...????不对吧?
select a.id.... from table1 a,table2 b,table 3
where a.id = b.id(+)
and a.id = c.id(+)
主要是編寫的思路能不能理清
from 後數據量小的寫在最後,依次往from靠近
注:先把有帶條件的數據找出來,盡量減小搜索范圍select ...from
(
select ... from
(select 要打的字段 from a,b where a.id =b.id) ab,c
)abc ,d
where .......
.
.
.
.