meidu2021/daju/target/classes/cn/com/lzt/distributionstats/sql/DistributionStatsMinidaoDao_ComDistributionStatsData.sql

SELECT e.postid,peopleNum ,IFNULL(CONVERT(peopleNum/comTotal*100,decimal(16,2)),0) proportion ,:param.yearmonth yearmonth from (
	SELECT in_postid postid ,COUNT(*) peopleNum, 
		(
			SELECT COUNT(*) as comToal
			from t_bus_user_personnel up
			LEFT JOIN t_bus_post bp
		    ON up.in_postid = bp.id
			LEFT JOIN 
			(
				SELECT userid,MAX(create_date) leave_date from t_bus_leave 
				WHERE delete_flag = 0
				GROUP BY userid
			) l
	        ON l.userid = up.userid
	        LEFT JOIN t_s_base_user u
			ON up.userid = u.ID
			WHERE (up.position_status = 1 or up.position_status = 2 or up.position_status = 3) and up.contract_stime <= :param.endmonth and (l.leave_date is null  or  l.leave_date >= :param.earlymonth ) and up.in_postid !='' and u.delete_flag =0 
		) comTotal 
		from t_bus_user_personnel bup
		LEFT JOIN t_bus_post bp
		ON bup.in_postid = bp.id
		LEFT JOIN 
		(
			SELECT userid,MAX(create_date) leave_date from t_bus_leave 
			WHERE delete_flag = 0
			GROUP BY userid
		) l
	    ON l.userid = bup.userid
	  	LEFT JOIN t_s_base_user u
		ON bup.userid = u.ID
		WHERE (bup.position_status = 1 or bup.position_status = 2 or bup.position_status = 3) and  bup.contract_stime <= :param.endmonth and (l.leave_date is null  or  l.leave_date >= :param.earlymonth ) and bup.in_postid !='' and u.delete_flag =0  
		GROUP BY in_postid
)e