MySql交叉表

钉钉的考勤，需要做个交叉表显示考勤记录，可以看看；

DELIMITER $$

USE `iopoa`$$

DROP PROCEDURE IF EXISTS `dingkqdaily_excel_sp`$$

CREATE DEFINER=`shenqi`@`%` PROCEDURE `dingkqdaily_excel_sp`(IN date_p VARCHAR(10),IN path_p VARCHAR(50),OUT totalCount INT)
BEGIN
    IF IFNULL(date_p,'')='' THEN SET date_p=DATE_FORMAT(NOW(),'%Y-%m-%d');
    END IF;
    IF IFNULL(path_p,'')='' THEN SET path_p='';
    END IF;
    SET @day=CONCAT(DATE_FORMAT(date_p,'%Y-%m'),'-01');
    SET @day2= LAST_DAY(@day);   
    SET @m=0;
    SET @n=DATEDIFF(@day2,@day);
    
    SET totalCount=@n;
    
    SET @str='select a.userid,b.name,if(a.checktype=\'OnDuty\',\'上午\',\'下午\') checktype';
    WHILE @m<=@n DO
        SET @day3=DATE_ADD(@day,INTERVAL @m DAY);
        SET @str=CONCAT(@str,',MAX(IF(TO_DAYS(a.workdate)=TO_DAYS(\'',@day3,'\') ,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 ,\'\')) \'',DAY(@day3),'\'',CHAR(13));
        SET @m=@m+1;
    END WHILE;
    SET @str=CONCAT(@str,'FROM dingkqdaily a INNER JOIN dinguser b ON a.userid=b.userid INNER JOIN dingdepartment c ON b.departmentid=c.id
                WHERE b.isused=1 AND DATE_FORMAT(a.workdate,\'%Y-%m\')=\'',DATE_FORMAT(date_p,'%Y-%m'),'\' 
                AND (ifnull(\'',path_p,'\',\'\')=\'\' or c.id in(select id from dingdepartment where path like \'',path_p,'%\'))
                GROUP BY a.userid,a.checktype DESC');
    -- select @str;
    PREPARE sqlstr FROM @str;
    EXECUTE sqlstr;
    END$$

DELIMITER ;

CALL dingkqdaily_excel_sp('2018-07-01','00004',@total);

执行后，可以把结果导入到EXCEL中或者做其他操作，@total是显示本月天数用的，是给导出EXCEL时需要显示多少列使用

效果不错吧。

里面的SELECT @str;结果也看看吧，

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.id
                WHERE 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