SELECT  o.org_desc as '分公司', total.counttotal as '送修任务数',A1.counta as '首次下发成功',A2.counta as 'B端推修成功',A3.counta as 'C端推修成功',
B1.countb as 'A端失败-B端(超时)',giveup2.countgiveup2 as '客户抵厂未维修',giveup3.countgiveup3 as '客户未抵厂',B2.countb as '首次下发到B端',
B3.countb as 'C端失败到B端',giveup.countgiveup as 'B端放弃数',factory03.num03 as '业务VIP',factory02.num02 as '业务合作',factory01.num01 as '理赔ET'
 from et_org_info o LEFT JOIN
(select o.org_desc,count(t.task_id) as counttotal from et_repair_task t 
LEFT JOIN et_org_info o on t.org_id=o.org_code  WHERE  t.create_time>'2018-01-01' and t.create_time<'2018-02-01' GROUP  BY o.org_desc ) total on o.org_desc = total.org_desc
-- B端滞留数
LEFT JOIN
(select org_desc,taskflag,count(task_id) as countb from (
select 
DISTINCT case when temp.distribute_name='95505' then 'B2' when temp.distribute_name like '%公司%' then 'B1' ELSE  'B3' end as taskflag,
temp.org_desc,temp.task_id from 
(SELECT 
f.*
 FROM
(SELECT t.*,
             CASE WHEN @mid= task_id THEN @row:=@row+1 ELSE @row:=1 END rownum,
             @mid:= task_id MID 
FROM  (SELECT o.org_desc,t.task_id,
         td.create_time,
         td.`accept_account`,
         td.`accept_name`,
         td.`distribute_account`,
         td.`distribute_name`
   FROM et_repair_task t 
   LEFT JOIN et_org_info o ON t.org_id=o.org_code 
   LEFT JOIN et_repair_task_dispatch td ON td.task_id=t.task_id
   WHERE  t.create_time>'2018-01-01' and t.create_time<'2018-02-01'
     AND t.status!='10' 
     AND t.garage_id IS  NULL    
   GROUP BY o.org_desc,t.task_id,
         td.create_time,
         td.`accept_account`,
         td.`accept_name`,
         td.`distribute_account`,
         td.`distribute_name`
   ORDER BY  t.task_id
            ,td.create_time DESC
        ) t,
       (SELECT @row=0) r,
       (SELECT @mid= '') m
 ) f
WHERE f.rownum = 1) temp) final GROUP BY final.org_desc ,final.taskflag) B1 on o.org_desc = B1.org_desc and B1.taskflag='B1'
LEFT JOIN
(select org_desc,taskflag,count(task_id) as countb from (
select 
DISTINCT case when temp.distribute_name='95505' then 'B2' when temp.distribute_name like '%公司%' then 'B1' ELSE  'B3' end as taskflag,
temp.org_desc,temp.task_id from 
(SELECT 
f.*
 FROM
(SELECT t.*,
             CASE WHEN @mid= task_id THEN @row:=@row+1 ELSE @row:=1 END rownum,
             @mid:= task_id MID 
FROM  (SELECT o.org_desc,t.task_id,
         td.create_time,
         td.`accept_account`,
         td.`accept_name`,
         td.`distribute_account`,
         td.`distribute_name`
   FROM et_repair_task t 
   LEFT JOIN et_org_info o ON t.org_id=o.org_code 
   LEFT JOIN et_repair_task_dispatch td ON td.task_id=t.task_id
   WHERE  t.create_time>'2018-01-01' and t.create_time<'2018-02-01' 
     AND t.status!='10' 
     AND t.garage_id IS  NULL    
   GROUP BY o.org_desc,t.task_id,
         td.create_time,
         td.`accept_account`,
         td.`accept_name`,
         td.`distribute_account`,
         td.`distribute_name`
   ORDER BY  t.task_id
            ,td.create_time DESC
        ) t,
       (SELECT @row=0) r,
       (SELECT @mid= '') m
 ) f
WHERE f.rownum = 1) temp) final GROUP BY final.org_desc ,final.taskflag) B2 on o.org_desc = B2.org_desc and B2.taskflag='B2'
LEFT JOIN
(select org_desc,taskflag,count(task_id) as countb from (
select 
DISTINCT case when temp.distribute_name='95505' then 'B2' when temp.distribute_name like '%公司%' then 'B1' ELSE  'B3' end as taskflag,
temp.org_desc,temp.task_id from 
(SELECT 
f.*
 FROM
(SELECT t.*,
             CASE WHEN @mid= task_id THEN @row:=@row+1 ELSE @row:=1 END rownum,
             @mid:= task_id MID 
FROM  (SELECT o.org_desc,t.task_id,
         td.create_time,
         td.`accept_account`,
         td.`accept_name`,
         td.`distribute_account`,
         td.`distribute_name`
   FROM et_repair_task t 
   LEFT JOIN et_org_info o ON t.org_id=o.org_code 
   LEFT JOIN et_repair_task_dispatch td ON td.task_id=t.task_id
   WHERE  t.create_time>'2018-01-01' and t.create_time<'2018-02-01' 
     AND t.status!='10' 
     AND t.garage_id IS  NULL    
   GROUP BY o.org_desc,t.task_id,
         td.create_time,
         td.`accept_account`,
         td.`accept_name`,
         td.`distribute_account`,
         td.`distribute_name`
   ORDER BY  t.task_id
            ,td.create_time DESC
        ) t,
       (SELECT @row=0) r,
       (SELECT @mid= '') m
 ) f
WHERE f.rownum = 1) temp) final GROUP BY final.org_desc ,final.taskflag) B3  on o.org_desc = B3.org_desc and B3.taskflag='B3'
-- A端送修成功数
LEFT JOIN
(select org_desc,taskflag,count(task_id) as counta from 
(select DISTINCT
temp.org_desc,temp.task_id,case when temp.distribute_account='95505' then 'A1' when temp.distribute_account='客户自助' then 'A3' ELSE  'A2' end as taskflag   from 
(SELECT 
f.*
 FROM
(SELECT t.*,
             CASE WHEN @mid= task_id THEN @row:=@row+1 ELSE @row:=1 END rownum,
             @mid:= task_id MID 
FROM  (SELECT o.org_desc,t.task_id,
         td.create_time,
         td.`accept_account`,
         td.`accept_name`,
         td.`distribute_account`,
         td.`distribute_name`
   FROM et_repair_task t 
   LEFT JOIN et_org_info o ON t.org_id=o.org_code 
   LEFT JOIN et_repair_task_dispatch td ON td.task_id=t.task_id
   WHERE  t.create_time>'2018-01-01' and t.create_time<'2018-02-01'
     AND t.status!='10' 
     AND t.garage_id IS  not NULL    
   GROUP BY o.org_desc,t.task_id,
         td.create_time,
         td.`accept_account`,
         td.`accept_name`,
         td.`distribute_account`,
         td.`distribute_name`
   ORDER BY  t.task_id
            ,td.create_time DESC
        ) t,
       (SELECT @row=0) r,
       (SELECT @mid= '') m
 ) f
WHERE f.rownum = 1) temp) final GROUP BY final.org_desc,final.taskflag) A1 on o.org_desc = A1.org_desc and A1.taskflag='A1'
  LEFT JOIN
  (select org_desc,taskflag,count(task_id) as counta from 
(select DISTINCT
temp.org_desc,temp.task_id,case when temp.distribute_account='95505' then 'A1' when temp.distribute_account='客户自助' then 'A3' ELSE  'A2' end as taskflag   from 
(SELECT 
f.*
 FROM
(SELECT t.*,
             CASE WHEN @mid= task_id THEN @row:=@row+1 ELSE @row:=1 END rownum,
             @mid:= task_id MID 
FROM  (SELECT o.org_desc,t.task_id,
         td.create_time,
         td.`accept_account`,
         td.`accept_name`,
         td.`distribute_account`,
         td.`distribute_name`
   FROM et_repair_task t 
   LEFT JOIN et_org_info o ON t.org_id=o.org_code 
   LEFT JOIN et_repair_task_dispatch td ON td.task_id=t.task_id
   WHERE  t.create_time>'2018-01-01' and t.create_time<'2018-02-01'
     AND t.status!='10' 
     AND t.garage_id IS  not NULL    
   GROUP BY o.org_desc,t.task_id,
         td.create_time,
         td.`accept_account`,
         td.`accept_name`,
         td.`distribute_account`,
         td.`distribute_name`
   ORDER BY  t.task_id
            ,td.create_time DESC
        ) t,
       (SELECT @row=0) r,
       (SELECT @mid= '') m
 ) f
WHERE f.rownum = 1) temp) final GROUP BY final.org_desc,final.taskflag ) A2  on o.org_desc = A2.org_desc and A2.taskflag='A2'
left join(
select org_desc,taskflag,count(task_id) as counta from 
(select DISTINCT
temp.org_desc,temp.task_id,case when temp.distribute_account='95505' then 'A1' when temp.distribute_account='客户自助' then 'A3' ELSE  'A2' end as taskflag   from 
(SELECT 
f.*
 FROM
(SELECT t.*,
             CASE WHEN @mid= task_id THEN @row:=@row+1 ELSE @row:=1 END rownum,
             @mid:= task_id MID 
FROM  (SELECT o.org_desc,t.task_id,
         td.create_time,
         td.`accept_account`,
         td.`accept_name`,
         td.`distribute_account`,
         td.`distribute_name`
   FROM et_repair_task t 
   LEFT JOIN et_org_info o ON t.org_id=o.org_code 
   LEFT JOIN et_repair_task_dispatch td ON td.task_id=t.task_id
   WHERE t.create_time>'2018-01-01' and t.create_time<'2018-02-01'
     AND t.status!='10' 
     AND t.garage_id IS  not NULL    
   GROUP BY o.org_desc,t.task_id,
         td.create_time,
         td.`accept_account`,
         td.`accept_name`,
         td.`distribute_account`,
         td.`distribute_name`
   ORDER BY  t.task_id
            ,td.create_time DESC
        ) t,
       (SELECT @row=0) r,
       (SELECT @mid= '') m
 ) f
WHERE f.rownum = 1) temp) final GROUP BY final.org_desc,final.taskflag ) A3  on o.org_desc = A3.org_desc and A3.taskflag='A3'
left join (
-- B端放弃数 
select o.org_desc,COUNT(t.task_id)  as countgiveup from et_repair_task t LEFT JOIN et_org_info o on t.org_id=o.org_code 
WHERE  t.create_time>'2018-01-01' and t.create_time<'2018-02-01' and t.status='10' GROUP BY o.org_desc ORDER BY o.org_desc ) giveup on o.org_desc = giveup.org_desc
left join (
-- B端客户抵厂未维修 
select o.org_desc,COUNT(t.task_id) as countgiveup2 from (
 select temp.* from (
  select * from et_repair_task_dispatch WHERE  create_time>'2018-01-01' and create_time<'2018-02-01' and status='2' order by create_time desc
  ) temp group by temp.task_id
 ) t
LEFT JOIN et_org_info o on t.org_code=o.org_code 
 GROUP BY o.org_desc ORDER BY o.org_desc ) giveup2 on o.org_desc = giveup2.org_desc
left join (
-- B端客户未抵厂
select o.org_desc,COUNT(t.task_id) as countgiveup3 from (
 select temp.* from (
  select * from et_repair_task_dispatch WHERE  create_time>'2018-01-01' and create_time<'2018-02-01' and status='3' order by create_time desc
  ) temp group by temp.task_id
 ) t
LEFT JOIN et_org_info o on t.org_code=o.org_code 
GROUP BY o.org_desc ORDER BY o.org_desc ) giveup3 on o.org_desc = giveup3.org_desc
left join (
-- 业务VIP
select o.org_desc,t.num as num03 from
(select task.org_id,count(task.task_id) as num from et_repair_task task left join et_garage et on task.garage_code=et.garage_code
where task.create_time>'2018-01-01' and task.create_time<'2018-02-01' and task.`status` in ('5','6','7')
and et.cooperate_type='03' group by task.org_id ) t left join
et_org_info o on t.org_id=o.org_code 
) factory03 on o.org_desc = factory03.org_desc
left join (
-- 业务合作
select o.org_desc,t.num as num02 from
(select task.org_id,count(task.task_id) as num from et_repair_task task left join et_garage et on task.garage_code=et.garage_code
where task.create_time>'2018-01-01' and task.create_time<'2018-02-01' and task.`status` in ('5','6','7')
and et.cooperate_type='02' group by task.org_id ) t left join
et_org_info o on t.org_id=o.org_code 
) factory02 on o.org_desc = factory02.org_desc
left join (
-- 理赔ET
select o.org_desc,t.num as num01 from
(select task.org_id,count(task.task_id) as num from et_repair_task task left join et_garage et on task.garage_code=et.garage_code
where task.create_time>'2018-01-01' and task.create_time<'2018-02-01' and task.`status` in ('5','6','7')
and et.cooperate_type='01' group by task.org_id ) t left join
et_org_info o on t.org_id=o.org_code 
) factory01 on o.org_desc = factory01.org_desc