select * from(
select * from(
select mpa02 ,count(a) as '总采购笔数',sum(CONVERT(int,a)) as '<24小时发货',sum(CONVERT(int,b)) as '24-36小时之间发货',
sum(CONVERT(int,c)) as '36-48小时之间发货',sum(CONVERT(int,d)) as '>48小时发货' ,
((round(sum(CONVERT(float,a))/COUNT(a)*0.7,2))+(round(sum(CONVERT(float,b))/COUNT(a)*0.56,2))+ (round(sum(CONVERT(float,c))/COUNT(a)*0.42,2))+
(round(sum(CONVERT(float,d))/COUNT(a)*0,2)))*100 as '发货得分' from (
select mpa02,
(case when datediff(HH,mpa05,mpaa02) <='24' then '1' else '' end ) as 'a' ,
(case when datediff(HH,mpa05,mpaa02) >'24' and DATEDIFF(HH,mpa05,mpaa02)<=36 then '1' else '' end ) as 'b',
(case when datediff(HH,mpa05,mpaa02) >'36' and DATEDIFF(HH,mpa05,mpaa02)<=48 then '1' else '' end ) as 'c',
(case when datediff(HH,mpa05,mpaa02) >'48' then '0' else '' end ) as 'd'

from mpa_file left join mpaa_file on mpa01=mpaa01 left join stq_file on mpa01=stq03 left join maa_file on mpa02=maa12 where mpa02='ZLDZSW'
and mpaacti='Y' and mpa03>='3' and mpa01 like 'MP%'
and (mpa04>='2017-07-13' and mpa04<'2017-08-13'))aa group by mpa02 )a left join
(select mpa02,count(distinct(stq03)) as '发货异常笔数' from mpa_file left join stq_file on mpa01=stq03 where mpa02='ZLDZSW'
and (mpa04>='2017-07-08' and mpa04<'2017-08-13')
and mpaacti='Y' and mpa03>='3' and mpa01 like 'MP%' group by mpa02) b on a.mpa02=b.mpa02 )c left join
(select mpa02,COUNT (distinct(maa12)) as '下架暂缺' from mpa_file left join maa_file on mpa02=maa12 where maa12='ZLDZSW'
and (mpa04>='2017-07-08' and mpa04<'2017-08-13')
and mpaacti='Y' and mpa03>='3' and mpa01 like 'MP%' group by mpa02 ) c on a.mpa02=c.mpa02 )