2
回答
Mysql sql优化问题 sql执行顺序问题???
利用AWS快速构建适用于生产的无服务器应用程序,免费试用12个月>>>   

下面是我的sql,这条语句执行需要4分钟左右,其中关联的表中数据加起来也就9万条,感觉这个sql还有待与优化,希望懂mysql语法的高人给指点一下,感谢啦!同时感觉left join 这个在sql中应该是有先后顺序,但是具体不清楚,希望有知道的给点指导,谢谢

SELECT bdp.`NO` AS '分部编号',bdp.`NAME` AS '分部名称', item.ITEM_NO AS  '物料编号',
item.ITEM_NAME AS '物料名称' ,bdu.`NAME`  AS '单位',
SUM(item.ITEM_AMOUNT/bdpr.MAIN2BASIC)  AS '消耗量',
ivts.PRICE  AS '单位成本' ,SUM(item.ITEM_AMOUNT/bdpr.MAIN2BASIC) *ivts.PRICE AS '小计'
FROM `prod_material_obtain` AS obtain
LEFT JOIN  prod_material_obtain_item AS item ON obtain.ID = item.HEADER_ID
LEFT JOIN  bd_product AS bdpr ON bdpr.`NO` = item.ITEM_NO
LEFT JOIN  bd_branch AS bdp  ON obtain.BRANCH_ID = bdp.ID
LEFT JOIN  bd_unit AS bdu ON bdu.ID = item.UNIT
LEFT JOIN  ivt_item_storage  AS ivts ON ivts.WAREHOUSE_ID = item.OUT_WAREHOUSE_ID AND item.ITEM_NO=ivts.ITEM_NO
where   obtain.CREATE_TIME >'2013-02-27-9' and  obtain.REMARK is not NULL
GROUP BY bdp.`NO`, item.ITEM_NO ;








举报
pengcheng_1024
发帖于5年前 2回/1K+阅
顶部