sql拼接查询

 <!--月度考勤汇总list列表-->
    <select id="queryAttendanceSummaryList" resultType="org.jeecg.modules.oa.vo.AttendanceSummaryMonthVo">
        SELECT
            a1.id AS employeeId,
            a1.organization_id AS organizationId,
            a1.department AS department,
            a1.employee_number AS employeeNumber,
            a1.position AS position,
            IFNULL((select count(distinct DATE_FORMAT(checktime, '%Y-%m-%d')) from oa_checkin where user_number = a1.employee_number AND DATE_FORMAT(checktime, '%Y-%m') = #{date}),0) as 'attendanceDays',
 <!--查询当前月天数（2020-09-01）传入值带年月日
     当前月天数减去打卡天数，值为未打卡天数-->
            IFNULL((SELECT DAYOFMONTH(LAST_DAY(#{dateNum}))-IFNULL((select count(distinct DATE_FORMAT(checktime, '%Y-%m-%d')) from oa_checkin where user_number = a1.employee_number AND DATE_FORMAT(checktime, '%Y-%m') = #{date}),0)),0) as 'notClockedDays',
 <!--计算请假天数，人员id=申请人id -->
            IFNULL((SELECT sum(days) from oa_attendance b where b.user_id = a1.id and b.approval_status = '3' and b.oa_type = '1' AND DATE_FORMAT(start_time, '%Y-%m') = #{date}),0) as 'vacationDays',
            IFNULL((SELECT sum(days) from oa_attendance b where b.user_id =  a1.id and b.approval_status = '3' and b.oa_type = '2' AND DATE_FORMAT(start_time, '%Y-%m') = #{date}),0) as 'outworkDays',
            IFNULL((SELECT sum(days) from oa_attendance b where b.user_id =  a1.id and b.approval_status = '3' and b.oa_type = '3' AND DATE_FORMAT(start_time, '%Y-%m') = #{date}),0) as 'travelDays',

            IFNULL((SELECT sum(days) from oa_overtime_leave b where b.employee_id =  a1.id and b.approval_status = '3' and b.overtime_leave_type = '1' AND DATE_FORMAT(start_time, '%Y-%m') = #{date}),0) as 'overtimeDays',
            IFNULL((SELECT sum(days) from oa_overtime_leave b where b.employee_id =  a1.id and b.approval_status = '3' and b.overtime_leave_type = '2' AND DATE_FORMAT(start_time, '%Y-%m') = #{date}),0) as 'leaveOffDays'
        FROM
             org_employee a1
        WHERE
            a1.state = '0' and a1.del_flag = '0'
        <if test="department != null and department != ''">
            AND a1.department = #{department}
        </if>
        <if test="employeeId != null and employeeId != ''">
            AND a1.id = #{employeeId}
        </if>
        <if test="organizationId != null and organizationId != ''">
            AND a1.organization_id = #{organizationId}
        </if>
        ORDER BY (attendanceDays+0) desc
    </select>