考勤表sql

<?_xml version=”1.0” encoding=”UTF-8”?>
<!DOCTYPE mapper PUBLIC “-//mybatis.org//DTD Mapper 3.0//EN” “http://mybatis.org/dtd/mybatis-3-mapper.dtd“ >


SELECT si.staff_id AS staff_id, IF(si.position = ‘巡检员’,si.staff_name,NULL) AS ins_name, r.road_name AS road_name, COUNT(att.staff_id OR NULL) AS att_days, COUNT(att.att_status = ‘正常’ OR NULL) AS reg_att_days, COUNT(att.att_status != ‘正常’ OR NULL) AS ab_att_days, (COUNT(att.att_status = ‘正常’ OR NULL))/(COUNT(att.staff_id OR NULL)) AS duty_rate FROM staff_info AS si INNER JOIN attendance AS att ON si.staff_id = att.staff_id INNER JOIN order_info AS oi ON oi.staff_id = att.staff_id INNER JOIN parking AS p ON p.parking_id = oi.parking_id LEFT JOIN road AS r ON r.road_id = p.road_id GROUP BY si.staff_name

SELECT att.staff_id AS staff_id FROM attendance AS att DATE_FORMAT(att.start_work,’%Y-%m-%d’) = #{d}

_ SELECT IFNULL(COUNT(DISTINCT(att.staff_id)),0) AS ins_qt FROM attendance AS att DATE_FORMAT(att.start_work,’%Y-%m-%d’) = DATE_FORMAT(#{d},’%Y-%m-%d’)

_<!-- 查询当月上班巡检员ID数组   --><br />    _<select id="selectMonthTargAttIds" resultType="java.lang.Integer"><br />        SELECT att.staff_id AS staff_id FROM attendance AS `att` WHERE DATE_FORMAT(`att`.start_work,'%Y-%m') = DATE_FORMAT(NOW(),'%Y-%m');<br />    </select>
_<!-- 查询上月上班巡检员ID数组   --><br />    _<select id="selectPreMonthTargAttIds" resultType="java.lang.Integer"><br />        SELECT att.staff_id AS staff_id FROM attendance AS `att` WHERE DATE_FORMAT(`att`.start_work,'%Y-%m') = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH),'%Y-%m');<br />    </select>
_<!-- 查询两月前巡检员ID数组   --><br />    _<select id="selectPre2MonthTargAttIds" resultType="java.lang.Integer"><br />        SELECT att.staff_id AS staff_id FROM attendance AS `att` WHERE DATE_FORMAT(`att`.start_work,'%Y-%m') = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 2 MONTH),'%Y-%m');<br />    </select>
_<!-- 查询当月巡检员人数   --><br />    _<select id="selectMonthAttQt" resultType="java.lang.Integer"><br />        SELECT IFNULL(COUNT(DISTINCT(att.`staff_id`)),0) AS ins_qt FROM attendance AS `att`<br />        WHERE DATE_FORMAT(`att`.start_work,'%Y-%m') = DATE_FORMAT(NOW(),'%Y-%m')<br />    </select>
_<!--  查询上月巡检员人数  --><br />    _<select id="selectPreMonthAttQt" resultType="java.lang.Integer"><br />        SELECT IFNULL(COUNT(DISTINCT(att.`staff_id`)),0) AS ins_qt FROM attendance AS `att`<br />        WHERE DATE_FORMAT(`att`.start_work,'%Y-%m') = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH),'%Y-%m')<br />    </select>
_<!--  查询两月前巡检员人数  --><br />    _<select id="selectPre2MonthAttQt" resultType="java.lang.Integer"><br />        SELECT IFNULL(COUNT(DISTINCT(att.`staff_id`)),0) AS ins_qt FROM attendance AS `att`<br />        WHERE DATE_FORMAT(`att`.start_work,'%Y-%m') = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 2 MONTH),'%Y-%m')<br />    </select><br /></mapper>