交叉表 mysql_MySql交叉表

SELECT a.userid,b.name,IF(a.checktype='OnDuty','上午','下午') checktype,MAX(IF(TO_DAYS(a.workdate)=TO_DAYS('2018-07-01') ,CASE a.timeresult WHEN 'Normal' THEN '√' WHEN 'Late' THEN CONCAT('迟到',TIMESTAMPDIFF(MINUTE,a.basechecktime,a.userchecktime),'分钟') WHEN 'Early' THEN CONCAT('早退',TIMESTAMPDIFF(MINUTE,a.userchecktime,a.basechecktime),'分钟') WHEN 'Absenteeism' THEN '旷工' WHEN 'NotSigned' THEN '未打卡' ELSE '' END ,'')) '1',MAX(IF(TO_DAYS(a.workdate)=TO_DAYS('2018-07-02') ,CASE a.timeresult WHEN 'Normal' THEN '√' WHEN 'Late' THEN CONCAT('迟到',TIMESTAMPDIFF(MINUTE,a.basechecktime,a.userchecktime),'分钟') WHEN 'Early' THEN CONCAT('早退',TIMESTAMPDIFF(MINUTE,a.userchecktime,a.basechecktime),'分钟') WHEN 'Absenteeism' THEN '旷工' WHEN 'NotSigned' THEN '未打卡' ELSE '' END ,'')) '2',MAX(IF(TO_DAYS(a.workdate)=TO_DAYS('2018-07-03') ,CASE a.timeresult WHEN 'Normal' THEN '√' WHEN 'Late' THEN CONCAT('迟到',TIMESTAMPDIFF(MINUTE,a.basechecktime,a.userchecktime),'分钟') WHEN 'Early' THEN CONCAT('早退',TIMESTAMPDIFF(MINUTE,a.userchecktime,a.basechecktime),'分钟') WHEN 'Absenteeism' THEN '旷工' WHEN 'NotSigned' THEN '未打卡' ELSE '' END ,'')) '3',MAX(IF(TO_DAYS(a.workdate)=TO_DAYS('2018-07-04') ,CASE a.timeresult WHEN 'Normal' THEN '√' WHEN 'Late' THEN CONCAT('迟到',TIMESTAMPDIFF(MINUTE,a.basechecktime,a.userchecktime),'分钟') WHEN 'Early' THEN CONCAT('早退',TIMESTAMPDIFF(MINUTE,a.userchecktime,a.basechecktime),'分钟') WHEN 'Absenteeism' THEN '旷工' WHEN 'NotSigned' THEN '未打卡' ELSE '' END ,'')) '4',MAX(IF(TO_DAYS(a.workdate)=TO_DAYS('2018-07-05') ,CASE a.timeresult WHEN 'Normal' THEN '√' WHEN 'Late' THEN CONCAT('迟到',TIMESTAMPDIFF(MINUTE,a.basechecktime,a.userchecktime),'分钟') WHEN 'Early' THEN CONCAT('早退',TIMESTAMPDIFF(MINUTE,a.userchecktime,a.basechecktime),'分钟') WHEN 'Absenteeism' THEN '旷工' WHEN 'NotSigned' THEN '未打卡' ELSE '' END ,'')) '5',MAX(IF(TO_DAYS(a.workdate)=TO_DAYS('2018-07-06') ,CASE a.timeresult WHEN 'Normal' THEN '√' WHEN 'Late' THEN CONCAT('迟到',TIMESTAMPDIFF(MINUTE,a.basechecktime,a.userchecktime),'分钟') WHEN 'Early' THEN CONCAT('早退',TIMESTAMPDIFF(MINUTE,a.userchecktime,a.basechecktime),'分钟') WHEN 'Absenteeism' THEN '旷工' WHEN 'NotSigned' THEN '未打卡' ELSE '' END ,'')) '6',MAX(IF(TO_DAYS(a.workdate)=TO_DAYS('2018-07-07') ,CASE a.timeresult WHEN 'Normal' THEN '√' WHEN 'Late' THEN CONCAT('迟到',TIMESTAMPDIFF(MINUTE,a.basechecktime,a.userchecktime),'分钟') WHEN 'Early' THEN CONCAT('早退',TIMESTAMPDIFF(MINUTE,a.userchecktime,a.basechecktime),'分钟') WHEN 'Absenteeism' THEN '旷工' WHEN 'NotSigned' THEN '未打卡' ELSE '' END ,'')) '7',MAX(IF(TO_DAYS(a.workdate)=TO_DAYS('2018-07-08') ,CASE a.timeresult WHEN 'Normal' THEN '√' WHEN 'Late' THEN CONCAT('迟到',TIMESTAMPDIFF(MINUTE,a.basechecktime,a.userchecktime),'分钟') WHEN 'Early' THEN CONCAT('早退',TIMESTAMPDIFF(MINUTE,a.userchecktime,a.basechecktime),'分钟') WHEN 'Absenteeism' THEN '旷工' WHEN 'NotSigned' THEN '未打卡' ELSE '' END ,'')) '8',MAX(IF(TO_DAYS(a.workdate)=TO_DAYS('2018-07-09') ,CASE a.timeresult WHEN 'Normal' THEN '√' WHEN 'Late' THEN CONCAT('迟到',TIMESTAMPDIFF(MINUTE,a.basechecktime,a.userchecktime),'分钟') WHEN 'Early' THEN CONCAT('早退',TIMESTAMPDIFF(MINUTE,a.userchecktime,a.basechecktime),'分钟') WHEN 'Absenteeism' THEN '旷工' WHEN 'NotSigned' THEN '未打卡' ELSE '' END ,'')) '9',MAX(IF(TO_DAYS(a.workdate)=TO_DAYS('2018-07-10') ,CASE a.timeresult WHEN 'Normal' THEN '√' WHEN 'Late' THEN CONCAT('迟到',TIMESTAMPDIFF(MINUTE,a.basechecktime,a.userchecktime),'分钟') WHEN 'Early' THEN CONCAT('早退',TIMESTAMPDIFF(MINUTE,a.userchecktime,a.basechecktime),'分钟') WHEN 'Absenteeism' THEN '旷工' WHEN 'NotSigned' THEN '未打卡' ELSE '' END ,'')) '10',MAX(IF(TO_DAYS(a.workdate)=TO_DAYS('2018-07-11') ,CASE a.timeresult WHEN 'Normal' THEN '√' WHEN 'Late' THEN CONCAT('迟到',TIMESTAMPDIFF(MINUTE,a.basechecktime,a.userchecktime),'分钟') WHEN 'Early' THEN CONCAT('早退',TIMESTAMPDIFF(MINUTE,a.userchecktime,a.basechecktime),'分钟') WHEN 'Absenteeism' THEN '旷工' WHEN 'NotSigned' THEN '未打卡' ELSE '' END ,'')) '11',MAX(IF(TO_DAYS(a.workdate)=TO_DAYS('2018-07-12') ,CASE a.timeresult WHEN 'Normal' THEN '√' WHEN 'Late' THEN CONCAT('迟到',TIMESTAMPDIFF(MINUTE,a.basechecktime,a.userchecktime),'分钟') WHEN 'Early' THEN CONCAT('早退',TIMESTAMPDIFF(MINUTE,a.userchecktime,a.basechecktime),'分钟') WHEN 'Absenteeism' THEN '旷工' WHEN 'NotSigned' THEN '未打卡' ELSE '' END ,'')) '12',MAX(IF(TO_DAYS(a.workdate)=TO_DAYS('2018-07-13') ,CASE a.timeresult WHEN 'Normal' THEN '√' WHEN 'Late' THEN CONCAT('迟到',TIMESTAMPDIFF(MINUTE,a.basechecktime,a.userchecktime),'分钟') WHEN 'Early' THEN CONCAT('早退',TIMESTAMPDIFF(MINUTE,a.userchecktime,a.basechecktime),'分钟') WHEN 'Absenteeism' THEN '旷工' WHEN 'NotSigned' THEN '未打卡' ELSE '' END ,'')) '13',MAX(IF(TO_DAYS(a.workdate)=TO_DAYS('2018-07-14') ,CASE a.timeresult WHEN 'Normal' THEN '√' WHEN 'Late' THEN CONCAT('迟到',TIMESTAMPDIFF(MINUTE,a.basechecktime,a.userchecktime),'分钟') WHEN 'Early' THEN CONCAT('早退',TIMESTAMPDIFF(MINUTE,a.userchecktime,a.basechecktime),'分钟') WHEN 'Absenteeism' THEN '旷工' WHEN 'NotSigned' THEN '未打卡' ELSE '' END ,'')) '14',MAX(IF(TO_DAYS(a.workdate)=TO_DAYS('2018-07-15') ,CASE a.timeresult WHEN 'Normal' THEN '√' WHEN 'Late' THEN CONCAT('迟到',TIMESTAMPDIFF(MINUTE,a.basechecktime,a.userchecktime),'分钟') WHEN 'Early' THEN CONCAT('早退',TIMESTAMPDIFF(MINUTE,a.userchecktime,a.basechecktime),'分钟') WHEN 'Absenteeism' THEN '旷工' WHEN 'NotSigned' THEN '未打卡' ELSE '' END ,'')) '15',MAX(IF(TO_DAYS(a.workdate)=TO_DAYS('2018-07-16') ,CASE a.timeresult WHEN 'Normal' THEN '√' WHEN 'Late' THEN CONCAT('迟到',TIMESTAMPDIFF(MINUTE,a.basechecktime,a.userchecktime),'分钟') WHEN 'Early' THEN CONCAT('早退',TIMESTAMPDIFF(MINUTE,a.userchecktime,a.basechecktime),'分钟') WHEN 'Absenteeism' THEN '旷工' WHEN 'NotSigned' THEN '未打卡' ELSE '' END ,'')) '16',MAX(IF(TO_DAYS(a.workdate)=TO_DAYS('2018-07-17') ,CASE a.timeresult WHEN 'Normal' THEN '√' WHEN 'Late' THEN CONCAT('迟到',TIMESTAMPDIFF(MINUTE,a.basechecktime,a.userchecktime),'分钟') WHEN 'Early' THEN CONCAT('早退',TIMESTAMPDIFF(MINUTE,a.userchecktime,a.basechecktime),'分钟') WHEN 'Absenteeism' THEN '旷工' WHEN 'NotSigned' THEN '未打卡' ELSE '' END ,'')) '17',MAX(IF(TO_DAYS(a.workdate)=TO_DAYS('2018-07-18') ,CASE a.timeresult WHEN 'Normal' THEN '√' WHEN 'Late' THEN CONCAT('迟到',TIMESTAMPDIFF(MINUTE,a.basechecktime,a.userchecktime),'分钟') WHEN 'Early' THEN CONCAT('早退',TIMESTAMPDIFF(MINUTE,a.userchecktime,a.basechecktime),'分钟') WHEN 'Absenteeism' THEN '旷工' WHEN 'NotSigned' THEN '未打卡' ELSE '' END ,'')) '18',MAX(IF(TO_DAYS(a.workdate)=TO_DAYS('2018-07-19') ,CASE a.timeresult WHEN 'Normal' THEN '√' WHEN 'Late' THEN CONCAT('迟到',TIMESTAMPDIFF(MINUTE,a.basechecktime,a.userchecktime),'分钟') WHEN 'Early' THEN CONCAT('早退',TIMESTAMPDIFF(MINUTE,a.userchecktime,a.basechecktime),'分钟') WHEN 'Absenteeism' THEN '旷工' WHEN 'NotSigned' THEN '未打卡' ELSE '' END ,'')) '19',MAX(IF(TO_DAYS(a.workdate)=TO_DAYS('2018-07-20') ,CASE a.timeresult WHEN 'Normal' THEN '√' WHEN 'Late' THEN CONCAT('迟到',TIMESTAMPDIFF(MINUTE,a.basechecktime,a.userchecktime),'分钟') WHEN 'Early' THEN CONCAT('早退',TIMESTAMPDIFF(MINUTE,a.userchecktime,a.basechecktime),'分钟') WHEN 'Absenteeism' THEN '旷工' WHEN 'NotSigned' THEN '未打卡' ELSE '' END ,'')) '20',MAX(IF(TO_DAYS(a.workdate)=TO_DAYS('2018-07-21') ,CASE a.timeresult WHEN 'Normal' THEN '√' WHEN 'Late' THEN CONCAT('迟到',TIMESTAMPDIFF(MINUTE,a.basechecktime,a.userchecktime),'分钟') WHEN 'Early' THEN CONCAT('早退',TIMESTAMPDIFF(MINUTE,a.userchecktime,a.basechecktime),'分钟') WHEN 'Absenteeism' THEN '旷工' WHEN 'NotSigned' THEN '未打卡' ELSE '' END ,'')) '21',MAX(IF(TO_DAYS(a.workdate)=TO_DAYS('2018-07-22') ,CASE a.timeresult WHEN 'Normal' THEN '√' WHEN 'Late' THEN CONCAT('迟到',TIMESTAMPDIFF(MINUTE,a.basechecktime,a.userchecktime),'分钟') WHEN 'Early' THEN CONCAT('早退',TIMESTAMPDIFF(MINUTE,a.userchecktime,a.basechecktime),'分钟') WHEN 'Absenteeism' THEN '旷工' WHEN 'NotSigned' THEN '未打卡' ELSE '' END ,'')) '22',MAX(IF(TO_DAYS(a.workdate)=TO_DAYS('2018-07-23') ,CASE a.timeresult WHEN 'Normal' THEN '√' WHEN 'Late' THEN CONCAT('迟到',TIMESTAMPDIFF(MINUTE,a.basechecktime,a.userchecktime),'分钟') WHEN 'Early' THEN CONCAT('早退',TIMESTAMPDIFF(MINUTE,a.userchecktime,a.basechecktime),'分钟') WHEN 'Absenteeism' THEN '旷工' WHEN 'NotSigned' THEN '未打卡' ELSE '' END ,'')) '23',MAX(IF(TO_DAYS(a.workdate)=TO_DAYS('2018-07-24') ,CASE a.timeresult WHEN 'Normal' THEN '√' WHEN 'Late' THEN CONCAT('迟到',TIMESTAMPDIFF(MINUTE,a.basechecktime,a.userchecktime),'分钟') WHEN 'Early' THEN CONCAT('早退',TIMESTAMPDIFF(MINUTE,a.userchecktime,a.basechecktime),'分钟') WHEN 'Absenteeism' THEN '旷工' WHEN 'NotSigned' THEN '未打卡' ELSE '' END ,'')) '24',MAX(IF(TO_DAYS(a.workdate)=TO_DAYS('2018-07-25') ,CASE a.timeresult WHEN 'Normal' THEN '√' WHEN 'Late' THEN CONCAT('迟到',TIMESTAMPDIFF(MINUTE,a.basechecktime,a.userchecktime),'分钟') WHEN 'Early' THEN CONCAT('早退',TIMESTAMPDIFF(MINUTE,a.userchecktime,a.basechecktime),'分钟') WHEN 'Absenteeism' THEN '旷工' WHEN 'NotSigned' THEN '未打卡' ELSE '' END ,'')) '25',MAX(IF(TO_DAYS(a.workdate)=TO_DAYS('2018-07-26') ,CASE a.timeresult WHEN 'Normal' THEN '√' WHEN 'Late' THEN CONCAT('迟到',TIMESTAMPDIFF(MINUTE,a.basechecktime,a.userchecktime),'分钟') WHEN 'Early' THEN CONCAT('早退',TIMESTAMPDIFF(MINUTE,a.userchecktime,a.basechecktime),'分钟') WHEN 'Absenteeism' THEN '旷工' WHEN 'NotSigned' THEN '未打卡' ELSE '' END ,'')) '26',MAX(IF(TO_DAYS(a.workdate)=TO_DAYS('2018-07-27') ,CASE a.timeresult WHEN 'Normal' THEN '√' WHEN 'Late' THEN CONCAT('迟到',TIMESTAMPDIFF(MINUTE,a.basechecktime,a.userchecktime),'分钟') WHEN 'Early' THEN CONCAT('早退',TIMESTAMPDIFF(MINUTE,a.userchecktime,a.basechecktime),'分钟') WHEN 'Absenteeism' THEN '旷工' WHEN 'NotSigned' THEN '未打卡' ELSE '' END ,'')) '27',MAX(IF(TO_DAYS(a.workdate)=TO_DAYS('2018-07-28') ,CASE a.timeresult WHEN 'Normal' THEN '√' WHEN 'Late' THEN CONCAT('迟到',TIMESTAMPDIFF(MINUTE,a.basechecktime,a.userchecktime),'分钟') WHEN 'Early' THEN CONCAT('早退',TIMESTAMPDIFF(MINUTE,a.userchecktime,a.basechecktime),'分钟') WHEN 'Absenteeism' THEN '旷工' WHEN 'NotSigned' THEN '未打卡' ELSE '' END ,'')) '28',MAX(IF(TO_DAYS(a.workdate)=TO_DAYS('2018-07-29') ,CASE a.timeresult WHEN 'Normal' THEN '√' WHEN 'Late' THEN CONCAT('迟到',TIMESTAMPDIFF(MINUTE,a.basechecktime,a.userchecktime),'分钟') WHEN 'Early' THEN CONCAT('早退',TIMESTAMPDIFF(MINUTE,a.userchecktime,a.basechecktime),'分钟') WHEN 'Absenteeism' THEN '旷工' WHEN 'NotSigned' THEN '未打卡' ELSE '' END ,'')) '29',MAX(IF(TO_DAYS(a.workdate)=TO_DAYS('2018-07-30') ,CASE a.timeresult WHEN 'Normal' THEN '√' WHEN 'Late' THEN CONCAT('迟到',TIMESTAMPDIFF(MINUTE,a.basechecktime,a.userchecktime),'分钟') WHEN 'Early' THEN CONCAT('早退',TIMESTAMPDIFF(MINUTE,a.userchecktime,a.basechecktime),'分钟') WHEN 'Absenteeism' THEN '旷工' WHEN 'NotSigned' THEN '未打卡' ELSE '' END ,'')) '30',MAX(IF(TO_DAYS(a.workdate)=TO_DAYS('2018-07-31') ,CASE a.timeresult WHEN 'Normal' THEN '√' WHEN 'Late' THEN CONCAT('迟到',TIMESTAMPDIFF(MINUTE,a.basechecktime,a.userchecktime),'分钟') WHEN 'Early' THEN CONCAT('早退',TIMESTAMPDIFF(MINUTE,a.userchecktime,a.basechecktime),'分钟') WHEN 'Absenteeism' THEN '旷工' WHEN 'NotSigned' THEN '未打卡' ELSE '' END ,'')) '31'

FROM dingkqdaily a INNER JOIN dinguser b ON a.userid=b.userid INNER JOIN dingdepartment c ON b.departmentid=c.idWHERE b.isused=1 AND DATE_FORMAT(a.workdate,'%Y-%m')='2018-07'

AND (IFNULL('00004','')='' OR c.id IN(SELECT id FROM dingdepartment WHERE path LIKE '00004%'))GROUP BY a.userid,a.checktype DESC