SQL内多个子查询的优化

SQL内多个子查询的优化

原SQL：

select DISTINCT c.gx_name,b.sort,IFNULL(a.prd_id,0) as prd_id,d.order_id,
        (select min(bb.actual_start_date)
        from prd_plan_dispatch aa
        inner join prd_plan_dispatch_detail bb on bb.dispatch_id = aa.id and bb.del_flag = '0'
        where aa.del_flag = '0'
        and aa.plan_detail_id = a.plan_detail_id
        and aa.gx_id = a.gx_id) as actualStartTime,

        (
        select min(start_date)
        from prd_plan_dispatch
        where plan_detail_id = a.plan_detail_id
        and gx_id = a.gx_id
        and del_flag = '0'
        ) as planStartTime,

        (
        select max(end_date)
        from prd_plan_dispatch
        where plan_detail_id = a.plan_detail_id
        and gx_id = a.gx_id
        and del_flag = '0'
        ) as planEndTime,

        (select max(bb.actual_end_date)
        from prd_plan_dispatch aa
        inner join prd_plan_dispatch_detail bb on bb.dispatch_id = aa.id and bb.del_flag = '0'
        where aa.del_flag = '0'
        and aa.plan_detail_id = a.plan_detail_id
        and aa.gx_id = a.gx_id) as actualEndTime,

        ifnull((select GROUP_CONCAT(sf_start)
        from prd_plan_dispatch
        where del_flag = '0'
        and plan_detail_id = a.plan_detail_id
        and gx_id = a.gx_id) ,0)as statusStr,

        (
        select sum(in_storage_qty)
        from prd_stock
        where del_flag = '0'
        and plan_detail_id = a.plan_detail_id
        and gx_id = a.gx_id
        ) as finishQty

        from prd_plan_process a
        inner join prd_tech_gx b on a.gx_id = b.gx_id and a.tech_id = b.tech_id
        inner join prd_gx_base c on c.id = a.gx_id
         inner join prd_plan_detail d on d.id = a.plan_detail_id and d.del_flag = '0'


        where a.del_flag = '0'
        order by prd_id ,b.sort

优化后：

把多个子查询放到一个查询里，再连接这个查询

select DISTINCT c.gx_name,b.sort,IFNULL(a.prd_id,0) as prd_id,d.order_id, 
temp.actualStartTime,temp.planStartTime,temp.planEndTime,temp.actualEndTime,ifnull(temp.statusStr,0) as statusStr,
   		(
        select sum(in_storage_qty)
        from prd_stock
        where del_flag = '0'
        and plan_detail_id = a.plan_detail_id
        and gx_id = a.gx_id
        ) as finishQty

 from prd_plan_process a
        inner join prd_tech_gx b on a.gx_id = b.gx_id and a.tech_id = b.tech_id
        inner join prd_gx_base c on c.id = a.gx_id
        inner join prd_plan_detail d on d.id = a.plan_detail_id and d.del_flag = '0'
		left join (		
            select aa.plan_detail_id ,aa.gx_id,min(bb.actual_start_date) as actualStartTime,
            	max(bb.actual_end_date) as actualEndTime,GROUP_CONCAT(DISTINCT aa.sf_start) as statusStr ,
            	min(aa.start_date) as planStartTime,max(aa.end_date) as planEndTime
            from prd_plan_dispatch aa
            left join prd_plan_dispatch_detail bb on bb.dispatch_id = aa.id and bb.del_flag = '0'
            where aa.del_flag = '0'
            group by aa.plan_detail_id,aa.gx_id
            
        ) temp on temp.plan_detail_id = a.plan_detail_id and temp.gx_id = a.gx_id
				 
where a.del_flag = '0'
order by prd_id ,b.sort