复杂SQL语句的优化

Question

　　在编写查询语句的时候，经常会遇到跨多个表查询数据的情况，我目前在给人用ACCESS写一个小程序，但是，我总感觉自己写的查询语句不够合理，各位能帮忙看一下吗，看看怎样修改一下效率更高，更符合SQL查询的逻辑。先谢过了。 SELECT IIf(Forms!控制面板!cboStage<>1,IIf(Forms!控制面板!cboStage=2,h.第一期期末现金,k.第二期期末现金),d.QMXJ) AS 期初现金,a.SaleIncome, a.OtherIncome, a.OtherCost, ((b.PreDeposit+a.BQCK)*b.DepositRate) AS 存款利息, a.AddLoan, a.Withdrawals,a.BQCLCGF, c.SJ, c.JS, c.GC, c.WL, c.SC, c.ZJL, b.AddLoanRate, b.CXFXS AS 促销费系数, b.YSFXS AS 产品运输费系数, b.GCFXS,(b.CNYSFXS*(IIf(Forms!控制面板!cboStage<>1,IIf(Forms!控制面板!cboStage=2,n.第一期期末材料余额,p.第二期期末材料余额),d.QMCLYE)+a.BQCLCGF-a.QMCLYE)) AS 厂内运输费,(b.CKBGF*a.QMCLYE) AS 仓库保管费, d.SaleIncome AS 期初销售收入, e.第一期销售收入, f.第二期销售收入, a.DeliveryAmount,IIF(Forms!控制面板!cboStage<>1,20,5) AS 研究开发费, a.InfoCost, a.AllFines,((b.PreLoan*b.PreLoanRate)+(a.AddLoan*b.AddLoanRate)) AS 借款利息, a.ReturnLoan, a.BQCK,IIf(Forms!控制面板!cboStage<>1,IIf(Forms!控制面板!cboStage=2,r.第一期所得税,s.第二期所得税),d.Tax) AS 上期所得税FROM(SELECT * FROM tblCompanyStages WHERE CompanyID=Forms!控制面板!cboCompany And StageNo=Forms!控制面板!cboStage)  AS a,(SELECT * FROM tblTrains WHERE TrainID=Forms!控制面板!txtTrainID)  AS b,(SELECT * FROM tblCompanys WHERE CompanyID=Forms!控制面板!cboCompany)  AS c,tblInitialReports AS d,(SELECT SaleIncome AS 第一期销售收入 FROM tblCompanyStages WHERE CompanyID=Forms!控制面板!cboCompany And StageNo=1)  AS e,(SELECT SaleIncome AS 第二期销售收入 FROM tblCompanyStages WHERE CompanyID=Forms!控制面板!cboCompany And StageNo=2)  AS f,(SELECT QMXJ AS 第一期期末现金 FROM tblCompanyStages WHERE CompanyID=Forms!控制面板!cboCompany And StageNo=1)  AS h,(SELECT QMXJ AS 第二期期末现金 FROM tblCompanyStages WHERE CompanyID=Forms!控制面板!cboCompany And StageNo=2)  AS k,(SELECT QMCLYE AS 第一期期末材料余额 FROM tblCompanyStages WHERE CompanyID=Forms!控制面板!cboCompany And StageNo=1)  AS n,(SELECT QMCLYE AS 第二期期末材料余额 FROM tblCompanyStages WHERE CompanyID=Forms!控制面板!cboCompany And StageNo=2)  AS p,(SELECT Tax AS 第一期所得税 FROM tblCompanyStages WHERE CompanyID=Forms!控制面板!cboCompany And StageNo=1)  AS r,(SELECT Tax AS 第二期所得税 FROM tblCompanyStages WHERE CompanyID=Forms!控制面板!cboCompany And StageNo=2)  AS s

Answer 1

从表结构上来优化吧，第一期期末现金、第二期期末材料余额、第二期所得税 等等数据应该单独存表

Answer 2

建议用临时表细化，现在看着就头痛

Answer 3

Access经不起这样的复杂语句啊，Access本来就慢

Answer 4

的确是，明显力不从心