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
mysql++百分比_mysql计算百分比
最新推荐文章于 2023-05-09 12:19:39 发布