sql多表查询优化

TableA(A1,A2)

TableB(B1,B2)

TableAB(id,A1,B1)

TableA和TableB是多对多关系，通过中间表TableAB连接

TableC(C1,A1,AmountC)

TableD(D1,A1,AmountD)

TableC和TableD通过A1字段和TableA连接,是多对一关系

 

第一种方法

select A1,A2

from TableA

left join 

(

select B1,B2,A1

from TableB

left join TableAB

on TableAB.B1 = TableB.B1

) TableBNew on TableBNew.A1 = TableA.A1

left join

(

select C1,sum(AmountC),A1

from TableC

group by A1

) TableCNew on TableCNew.A1 = TableA.A1

left join

(

select D1,sum(AmountD),A1

from TableD

group by A1

) TableDNew on TableDNew.A1 = TableA.A1

 

第二种方法

select TableANew2.A1,TableANew2.A2,TableANew2.AmountC,TableANew2.AmountD,TableB.B1,TableB.B2

from

(

select TableANew.A1,TableANew.A2,TableANew.AmountC,sum(TableD.AmountD)

from

(

select A1,A2,sum(TableC.AmountC) AmountC

from TableA

left join TableC

on TableC.A1 = TableA.A1

group by TableA.A1

) TableANew

left join TableD

on TableD.A1 = TableANew.A1

group by TableANew.A1

)TableANew2

left join TableAB

on TableAB.A1 = TableANew2.A1

left join TableB

on TableB.B1 = TableAB.B1

 

第一种方式通过子查询，索引很难发挥作用

第二种方式表逐层嵌入查询，通过对比测试，如果配合索引的话，比第一种方式快一个数量级以上。