select * from (
select r1.id, r1.rubbertype_id, r1.peiliaoCode, r1.lianjiaoren, r1.inDate, r1.inQuantity, r1.inNote, r1.outDate, r1.outQuantity, r1.product_id, r1.group_id, r1.liuhuaWorker_id, r1.isShenHe, r1.baozhiqi, r1.tuikuQuantity,rt.name as rtname ,w.name as lhname ,w.code,g.name as groupname,r2.kc,pro.code as pcode,r1.baozhiqi as bzq1,r1.writer,r1.lianjiaoren as ljr1 from rubberstore r1 left join (
select rs.rubbertype_id, rs.peiliaoCode,(ifnull(sum(rs.inquantity),0) +ifnull(sum(rs.tuikuquantity),0) - ifnull(sum(rs.outquantity),0) ) as kc
from rubberstore rs left join rubbertype rt on rt.id=rs.rubbertype_id group by rs.rubbertype_id, rs.peiliaoCode ) r2
on r2.rubbertype_id=r1.rubbertype_id and r2.peiliaoCode=r1.peiliaoCode
left join rubbertype rt on rt.id=r1.rubbertype_id
left join worker w on w.id = r1.liuhuaWorker_id
left join t_group g on g.id=r1.group_id
left join product pro on pro.id=r1.product_id) tp order by rubbertype_id
MySQL的数据库,5000多条数据,查询要1.5秒,请大家帮忙优化一下,谢谢
select r1.id, r1.rubbertype_id, r1.peiliaoCode, r1.lianjiaoren, r1.inDate, r1.inQuantity, r1.inNote, r1.outDate, r1.outQuantity, r1.product_id, r1.group_id, r1.liuhuaWorker_id, r1.isShenHe, r1.baozhiqi, r1.tuikuQuantity,rt.name as rtname ,w.name as lhname ,w.code,g.name as groupname,r2.kc,pro.code as pcode,r1.baozhiqi as bzq1,r1.writer,r1.lianjiaoren as ljr1 from rubberstore r1 left join (
select rs.rubbertype_id, rs.peiliaoCode,(ifnull(sum(rs.inquantity),0) +ifnull(sum(rs.tuikuquantity),0) - ifnull(sum(rs.outquantity),0) ) as kc
from rubberstore rs left join rubbertype rt on rt.id=rs.rubbertype_id group by rs.rubbertype_id, rs.peiliaoCode ) r2
on r2.rubbertype_id=r1.rubbertype_id and r2.peiliaoCode=r1.peiliaoCode
left join rubbertype rt on rt.id=r1.rubbertype_id
left join worker w on w.id = r1.liuhuaWorker_id
left join t_group g on g.id=r1.group_id
left join product pro on pro.id=r1.product_id) tp order by rubbertype_id
MySQL的数据库,5000多条数据,查询要1.5秒,请大家帮忙优化一下,谢谢
order by 很浪费时间。