<select id="queryPage" resultType="">
SELECT picc_user.*,
sys_dept.`name` AS deptName,
sum(IF(picc_score.score_expire_time > NOW(), picc_score.score_not_used, 0)) AS scoreNotUsed,
sum(picc_score.score_number) AS scoreCount
FROM picc_score
LEFT JOIN picc_user ON picc_user.id = picc_score.picc_user_id
LEFT JOIN sys_dept ON picc_score.dept_id = sys_dept.dept_id
<where>
<if test="deptId != null">
picc_score.dept_id = #{deptId}
</if>
<if test="piccUserId != null">
AND picc_score.picc_user_id = #{piccUserId}
</if>
<if test="phone!= null and phone!= ''">
<bind name="phone" value="'%' + phone + '%'"/>
AND picc_score.phone like #{phone}
</if>
</where>
GROUP BY picc_score.dept_id, picc_score.picc_user_id, sys_dept.`name`
</select>
<select id="getInfo" resultType="">
SELECT picc_user.*,
sys_dept.`name` AS deptName,
IFNULL((SELECT SUM(score_not_used)
FROM picc_score
WHERE dept_id = #{deptId}
AND picc_user_id = #{userId}
AND score_expire_time > NOW()), 0) AS scoreNotUsed,
sum(picc_score.score_number) AS scoreCount
FROM picc_score
LEFT JOIN picc_user ON picc_user.id = picc_score.picc_user_id
LEFT JOIN sys_dept ON picc_score.dept_id = sys_dept.dept_id
where picc_score.dept_id = #{deptId}
and picc_score.picc_user_id = #{userId}
GROUP BY picc_score.dept_id, picc_score.picc_user_id, sys_dept.`name`
</select>
以上两种查询方法,第一个是分页列表,第二个是单条数据详情(可以优化使用第一种的if条件)
sum(IF('条件判断','求和的字段','NULL不计算')) as '别名'