| 123456789101112131415161718192021222324 |
- select
- sum(a.total) as total,
- sum(a.certCount) as certCount,
- sum(a.percent) as percent
- from (select count(baseuu.realname) as total,
- count(uu2.id)as certCount,
- cast(count(uu2.id)/count(baseuu.realname) as decimal(10,2)) as percent,
- 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 pers.userid = uu.id
- left join t_s_depart dp on dp.ID = uorg.org_id
- left join t_bus_user_certificate uucert on uucert.userid = pers.userid
- <#if inDto.certId ?exists && inDto.certId ?length gt 0>
- and uucert.certificateid = :inDto.certId
- </#if>
- left join t_s_user uu2 on uucert.userid = uu2.id
- where pers.leave_date is null and baseuu.delete_flag = '0' and dp.glcname is not null
- <#if inDto.departname ?exists && inDto.departname ?length gt 0>
- and dp.glcname = :inDto.departname
- </#if>
- GROUP BY dp.glcname) a
|
