复杂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

的确是，明显力不从心