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

SELECT in_postid postid ,uo.org_id deptId,dept.pjtid,count(*) peoplenum
from t_bus_user_personnel bup
LEFT JOIN t_bus_post bp
ON bup.in_postid = bp.id	
LEFT JOIN t_s_user_org uo
ON bup.userid = uo.user_id and uo.`status`=0 and uo.ifpluralism = 0
LEFT JOIN t_s_depart d
ON d.ID = uo.org_id
LEFT JOIN 
(
	SELECT userid,MAX(create_date) leave_date from t_bus_leave 
	WHERE delete_flag = 0
	GROUP BY userid
) l
ON bup.userid = l.userid
LEFT JOIN t_s_base_user u
ON bup.userid = u.ID
LEFT JOIN 
(
	SELECT uw.belong_unitid deptId, uw.pjt_id pjtid
		from t_bus_user_personnel bup
		LEFT JOIN t_bus_user_wage uw
		ON bup.userid = uw.userid  and uw.monthly =:param.yearmonth
		LEFT JOIN t_s_depart d
		ON uw.pjt_id = d.ID and (d.org_type = 3 or d.org_type = 5)
		WHERE uw.belong_unitid != '' and uw.pjt_id !='' 
		GROUP BY uw.pjt_id,uw.belong_unitid
) dept ON dept.deptId = uo.org_id
WHERE (bup.position_status = 1 or bup.position_status = 2 or bup.position_status = 3) and  bup.contract_stime <=  :param.endmonth and uo.org_id != '' 
and (l.leave_date is NULL or l.leave_date >= :param.earlymonth ) and bup.in_postid !='' and u.delete_flag =0 
GROUP BY dept.pjtid,uo.org_id,in_postid