meidu2021/daju/target/classes/cn/com/lzt/dbquery/sql/DbQueryUserMiniDao_queryUserByHujidi.sql

select count(baseuu.realname) as total, 

count(uu3.id)as shanghai, 
count(uu4.id)as anhui,
count(uu5.id)as henan, 
count(uu6.id)as jangsu, 
count(uu7.id)as zhejiang, 
count(uu8.id)as notShanghai,  
count(uu9.id)as notFivePlaces,  

dp.glcname as departname
from t_bus_user_personnel pers
LEFT JOIN t_s_base_user baseuu on baseuu.id = pers.userid
left join t_s_user_org uorg on uorg.user_id = pers.userid and uorg.ifpluralism = '0'
left join t_s_user uu on baseuu.id = uu.id
left join t_s_depart dp on dp.ID = uorg.org_id
left join t_s_user uu3 on baseuu.id = uu3.id and uu3.hkaddress like '%上海%'
left join t_s_user uu4 on baseuu.id = uu4.id and uu4.hkaddress like '%安徽%'
left join t_s_user uu5 on baseuu.id = uu5.id and uu5.hkaddress like '%河南%'
left join t_s_user uu6 on baseuu.id = uu6.id and uu6.hkaddress like '%江苏%'
left join t_s_user uu7 on baseuu.id = uu7.id and uu7.hkaddress like '%浙江%'
left join t_s_user uu8 on baseuu.id = uu8.id and (uu8.hkaddress like '%浙江%' or uu8.hkaddress like '%安徽%' or uu8.hkaddress like '%河南%' or uu8.hkaddress like '%江苏%')
left join t_s_user uu9 on baseuu.id = uu9.id and (uu9.hkaddress not like '%河南%' and uu9.hkaddress not like '%上海%' and uu9.hkaddress not like '%安徽%' and uu9.hkaddress not like '%江苏%' and uu9.hkaddress not like '%浙江%')
where  pers.leave_date is null and baseuu.delete_flag = '0' and dp.glcname is not null 
GROUP BY dp.glcname