MySQL查询之sum() + group by查询sum()中增加条件

<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  别名