标签：count 传入 type work tempCount companyIds 主表 cw id
<select id="getUserDatas" resultType="com.shsajt.common.dto.UserDataWorkDTO">
SELECT temp.user_id,
ifnull(tempwap.count,0) as count
from
(
<if test ="userIds != null and userIds.size() >0">
<foreach collection="userIds" index="index" item="item" open="(" separator="union" close=")">
select #{item} as user_id
</foreach>
</if>
)
temp left join
(
select wap.user_id, count(*) as count
from
work_and_person wap
left join contract_work cw on wap.work_id = cw.id
left join closed_loop cl on cw.id = cl.work_id
<where>
wap.deleted = 0 and cw.deleted = 0 and cw.work_state_id = 3
<!-- 20231212 shen 把项目部Id作为查询条件，保持数据统一 -->
and cw.office_id = #{officeId}
and ( (cw.work_type_id = 8 and cl.looped = 1) or (cw.work_type_id != 8 and cl.looped is null) )
<if test ="userIds != null and userIds.size() >0">
and wap.user_id in
<foreach collection='userIds' item='item' index='index' open='(' separator=',' close=')'>
#{item}
</foreach>
</if>
</where>
GROUP BY wap.user_id
)
tempwap on temp.user_id = tempwap.user_id order by count desc
</select>
<select id="getWorkCompanyCount" resultType="com.shsajt.common.dto.WorkCompanyCount">
select
temp.companyId,
tempCount.checkNum,
tempCount.trainNum,
tempCount.guideNum,
tempCount.serveNum,
tempCount.meetNum,
tempCount.reportNum,
tempCount.otherNum
from
(
<if test ="companyIds != null and companyIds.size() >0">
<foreach collection="companyIds" index="index" item="item" open="(" separator="union" close=")">
select #{item} as companyId
</foreach>
</if>
) temp
left join
( select
wc.object_id as companyId,
count(distinct( case when cw.work_type_id =8 then cw.id end)) as checkNum,
count(distinct(case when cw.work_type_id =9 then cw.id end)) as trainNum,
count(distinct(case when cw.work_type_id =10 then cw.id end)) as guideNum,
count(distinct(case when cw.work_type_id =11 then cw.id end)) as serveNum,
count(distinct(case when cw.work_type_id =12 then cw.id end)) as meetNum,
count(distinct(case when cw.work_type_id =13 then cw.id end)) as reportNum,
count(distinct(case when cw.work_type_id =14 then cw.id end)) as otherNum
from work_and_object wc
left join contract_work cw on cw.id = wc.work_id
left join work_and_item wai on cw.id = wai.work_id
left join contract_item ci on wai.item_id = ci.id
where
cw.deleted = 0 and wc.deleted = 0 and cw.work_state_id = 3 and cw.serve_object = 2
<if test = "vo.beginDate != null and vo.endDate != null">
and cw.end_date between #{vo.beginDate} and #{vo.endDate}
</if>
<if test="vo.contractId != null">
and ci.contract_id = #{vo.contractId}
</if>
<if test ="companyIds != null and companyIds.size() >0">
and wc.object_id in
<foreach collection='companyIds' item='item' index='index' open='(' separator=',' close=')'>
#{item}
</foreach>
</if>
group by wc.object_id
) tempCount on temp.companyId = tempCount.companyId
</select>
标签：count,
传入,
type,
work,
tempCount,
companyIds,
主表,
cw,
id
From： https://www.cnblogs.com/zheng-s/p/18036746