各位大哥帮我看一下代码啊
本帖最后由 fly_wzg 于 2013-01-12 12:20:17 编辑 各位大哥下面的代码可以优化吗？这么弄的话查询数据量大的时候我心里没底啊。
另外这些字段那些需要建立索引呢。
sql = " select * from ";
sql += "(select * from ";//m
sql += "(select * from ";//k
sql += "(select * from ";//i
sql += "(select * from ";//g
sql += "(select * from ";//e
sql += "(select * from ";//c
sql += "(select * from Contract left outer join Client on Contract.ClientCode=Client.Code)a ";//合同信息、单位信息
sql += "left outer join (select Code as PCode,Name as PName from Personnel)b on a.Personnel=b.PCode)c ";//业务员代码、姓名
sql += "left outer join (select Code as LCode,Name as LName from Personnel)d on c.Leader=d.LCode)e ";//管理人员代码、姓名
sql += "left outer join (select CttN as ICttN,sum(CttMoney) as ICttMoney,sum(Money) as IMoney from Inventory group by CttN)f on e.CttN=f.ICttN)g ";//合同金额，结算金额
sql += "left outer join (select CttN as DCttN,sum(CttMoney) as DCttMoney,sum(Money) as DMoney from Delivery group by CttN)h on g.CttN=h.DCttN)i ";//发货金额，发货结算金额
sql += "left outer join (select CttN as BCttN,sum(Money) as BMoney from Bill group by CttN)j on i.CttN=j.BCttN)k ";//开票金额
sql += "left outer join (select CttN as CCttN,sum(Money) as CMoney from Cash group by CttN)l on k.CttN=l.CCttN)m ";//到账金额
sql += "left outer join (select CttN as CCttN,Img from Img)n on m.CttN=n.CCttN ";//合同文本
[解决办法]

Select 
a.*
,b.*
,c.Code As PCode
,c.Name As PName
,d.Code As LCode
,d.Name As LName
,e.CttN As ICttN
,SUM(e.CttMoney)As ICttMoney
,SUM(e.Money)As IMoney
,f.CttNAs DCttN 
 
,SUM(f.CttMoney)As DCttMoney
,SUM(f.Money)As DMoney
,g.CttNAs BCttN
,SUM(g.Money)As BMoney
,h.CttNAs CCttN
,SUM(h.Money)As CMoney
,i.CttNAs CCttN
,i.Img
from ContractAs a-- 8
Left Join ClientAs b On a.ClientCode=b.Code-- 8
Left Join PersonnelAs c On b.Personnel=c.Code-- 7
Left Join PersonnelAs d On c.Leader=d.Code-- 6
Left Join InventoryAs e On a.CttN=e.CttN-- 5
Left Join DeliveryAs f On a.CttN=f.CttN-- 4
Left Join BillAs g On a.CttN=g.CttN-- 3
Left Join CashAs h On a.CttN=h.CttN-- 2
Left Join ImgAs i On a.CttN=i.CttN-- 1
Group by 
a.*-- 自己上 Contract 的字段
,b.*-- 自己上 Client 的字段
,c.Code
,c.Name
,d.Code
,d.Name
,e.CttN
,f.CttN
,g.CttN
,h.CttN
,i.CttN
,i.Img