select distinct t1.id, t1.crm_id,t1.user_name,t1.area_name,t1.start_time,t1.end_time,t1.count,t1.qd,t1.wqd,t1.zqd,
(round(t1.qd/t1.zqd,2)+"%"),CONCAT(round(t1.qd/t1.zqd*100,2),'%'),
t.father_name,t.CHANNEL_NAME from
(select s1.user_id, s1.store_id, s2.CHANNEL_NBR,s2.CHANNEL_NAME,s4.OPERATORS_NBR,s4.father_name
from basic_user_store s1 INNER JOIN basic_crm_channel s2 on
s1.store_id=s2.ORG_ID INNER JOIN basic_crm_channel_store s3 ON s2.CHANNEL_NBR=s3.CHANNEL_NBR
INNER JOIN basic_crm_store s4 ON
s3.OPERATORS_NBR=s4.OPERATORS_NBR
where store_id in
(select store_id from basic_user_store where user_id = 'rick_lin' GROUP BY store_id)
) t INNER JOIN
(select crm_id,s.id ,user_name, area_name, start_time,end_time, (datediff(end_time, start_time)+1) count ,
(SELECT count(a.id) FROM arrange a INNER JOIN sign_info si ON a.user_id=si.sign_user
AND a.store=si.sign_store AND DATE_FORMAT(si.sign_date, '%Y-%m-%d' )=DATE_FORMAT(a.arrange_data, '%Y-%m-%d' )
WHERE state IS NOT NULL) qd ,
(SELECT count(a.id) FROM arrange a INNER JOIN sign_info si ON a.user_id=si.sign_user
AND a.store=si.sign_store AND DATE_FORMAT(si.sign_date, '%Y-%m-%d' )=DATE_FORMAT(a.arrange_data, '%Y-%m-%d' )
WHERE state IS NULL) wqd,
(SELECT count(a.id) FROM arrange a INNER JOIN sign_info si ON a.user_id=si.sign_user
AND a.store=si.sign_store AND DATE_FORMAT(si.sign_date, '%Y-%m-%d' )=DATE_FORMAT(a.arrange_data, '%Y-%m-%d' )
) zqd
from (select user_id from basic_role_user gs
INNER JOIN basic_role ga ON gs.role_id=ga.role_id AND ga.role_level > '6') t
LEFT JOIN sys_user s on s.id = t.user_id LEFT JOIN area gg on s.area=gg.id INNER JOIN arrange a ON s.id=a.user_id
INNER JOIN sign_info si ON a.start_time=si.sign_date and a.user_id=si.sign_user and a.store=si.sign_store
where s.is_delete =0 ) t1
on t1.crm_id = t.user_id
(round(t1.qd/t1.zqd,2)+"%"),CONCAT(round(t1.qd/t1.zqd*100,2),'%'),
t.father_name,t.CHANNEL_NAME from
(select s1.user_id, s1.store_id, s2.CHANNEL_NBR,s2.CHANNEL_NAME,s4.OPERATORS_NBR,s4.father_name
from basic_user_store s1 INNER JOIN basic_crm_channel s2 on
s1.store_id=s2.ORG_ID INNER JOIN basic_crm_channel_store s3 ON s2.CHANNEL_NBR=s3.CHANNEL_NBR
INNER JOIN basic_crm_store s4 ON
s3.OPERATORS_NBR=s4.OPERATORS_NBR
where store_id in
(select store_id from basic_user_store where user_id = 'rick_lin' GROUP BY store_id)
) t INNER JOIN
(select crm_id,s.id ,user_name, area_name, start_time,end_time, (datediff(end_time, start_time)+1) count ,
(SELECT count(a.id) FROM arrange a INNER JOIN sign_info si ON a.user_id=si.sign_user
AND a.store=si.sign_store AND DATE_FORMAT(si.sign_date, '%Y-%m-%d' )=DATE_FORMAT(a.arrange_data, '%Y-%m-%d' )
WHERE state IS NOT NULL) qd ,
(SELECT count(a.id) FROM arrange a INNER JOIN sign_info si ON a.user_id=si.sign_user
AND a.store=si.sign_store AND DATE_FORMAT(si.sign_date, '%Y-%m-%d' )=DATE_FORMAT(a.arrange_data, '%Y-%m-%d' )
WHERE state IS NULL) wqd,
(SELECT count(a.id) FROM arrange a INNER JOIN sign_info si ON a.user_id=si.sign_user
AND a.store=si.sign_store AND DATE_FORMAT(si.sign_date, '%Y-%m-%d' )=DATE_FORMAT(a.arrange_data, '%Y-%m-%d' )
) zqd
from (select user_id from basic_role_user gs
INNER JOIN basic_role ga ON gs.role_id=ga.role_id AND ga.role_level > '6') t
LEFT JOIN sys_user s on s.id = t.user_id LEFT JOIN area gg on s.area=gg.id INNER JOIN arrange a ON s.id=a.user_id
INNER JOIN sign_info si ON a.start_time=si.sign_date and a.user_id=si.sign_user and a.store=si.sign_store
where s.is_delete =0 ) t1
on t1.crm_id = t.user_id