mysql动态产生的日期行转列

右边红色部分为想要的效果，左边为现在代码实现的效果
img
img

CREATE TABLE `attendance_collect` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `per_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '人员姓名',
  `check_date` datetime DEFAULT NULL COMMENT '上传时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='人员统计';


INSERT INTO `attendance_collect` (`id`, `per_name`, `check_date`) VALUES (4,  '孙笑川',  '2021-07-30 10:54:21');
INSERT INTO `attendance_collect` (`id`, `per_name`, `check_date`) VALUES (5,  '孙笑川',  '2021-07-31 10:54:21');
INSERT INTO `attendance_collect` (`id`, `per_name`, `check_date`) VALUES (6,  '测试',  '2021-08-02 11:57:41');
INSERT INTO `attendance_collect` (`id`, `per_name`, `check_date`) VALUES (7,  '测试1',  '2021-08-02 14:06:58');
INSERT INTO `attendance_collect` (`id`, `per_name`, `check_date`) VALUES (8,  '测试1',  '2021-08-01 14:06:58');

下边为现在未实现想要的结果代码

SELECT
        u.perName AS perName,
        temp.date AS checkDate,
        IF(u.unmber=1,'√','×')
        FROM
        (
            SELECT date FROM
            (
                SELECT
                DATE_FORMAT( DATE_SUB( last_day( '2021-08-01' ), INTERVAL xc - 1 DAY ), '%Y-%m-%d' ) AS date
                FROM
                (
                    SELECT @xi := @xi + 1 AS xc
                    FROM
                    ( SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 ) xc1,
                    ( SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 ) xc2,
                    ( SELECT @xi := 0 ) xc0
                ) xcxc
            ) x0
            WHERE x0.date >= ( SELECT date_add( '2021-08-01', INTERVAL - DAY ( '2021-08-01' )+ 1 DAY ))
        ) temp
        LEFT JOIN
        (
            SELECT
            ac.per_name AS perName,
            CAST( ac.check_date AS date ) AS checkDate,
            COUNT( ac.check_date ) AS unmber
            FROM xzhix_base_data.attendance_collect ac
            GROUP BY CAST( ac.check_date AS date ), ac.per_name
        ) u
        ON temp.date = u.checkDate
        GROUP BY u.perName,temp.date
        ORDER BY temp.date;

行专列一般用case...when就能实现

已实现，使用存储过程创建临时表实现。
代码如下：

CREATE DEFINER=`root`@`%` PROCEDURE `getAttendanceCollectInfoByMonth`(IN perName varchar(1024),stime varchar(64))
    COMMENT '作业人员考勤统计'
BEGIN
    DROP TEMPORARY TABLE IF EXISTS __attendance_temp;
    SET @strTmpTable = CONCAT("SELECT
    IF(ISNULL(perName)=1,null,perName) AS perName,
    checkDate,
    date AS dtime,
    IF(ISNULL(checkDate),0,1) AS clockIn
FROM
    (
    SELECT
        date 
    FROM
        (
        SELECT
            DATE_FORMAT( DATE_SUB( last_day( '",stime,"' ), INTERVAL xc - 1 DAY ), '%Y-%m-%d' ) AS date 
        FROM
            (
            SELECT
                @xi := @xi + 1 AS xc 
            FROM
                ( SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 ) xc1,
                ( SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 ) xc2,
                ( SELECT @xi := 0 ) xc0 
            ) xcxc 
        ) x0 
    WHERE
        x0.date >= (
        SELECT
        date_add( '",stime,"', INTERVAL - DAY ( '",stime,"' )+ 1 DAY )) 
    ) temp
    LEFT JOIN (
    SELECT
        ac.per_name AS perName,
        CAST( ac.check_date AS date ) AS checkDate 
    FROM
        xzhix_base_data.attendance_collect ac
    ");
    IF(perName IS NOT NULL AND perName <> '') THEN
        SET @strTmpTable = CONCAT(@strTmpTable, ' WHERE 1=1 AND ac.per_name LIKE "%',perName,'%"');
    END IF;
    SET @strTmpTable = CONCAT(@strTmpTable, "GROUP BY
        CAST( ac.check_date AS date ),
    ac.per_name 
    ) u ON temp.date = u.checkDate
    ORDER BY temp.date");
--     SELECT @strTmpTable;
    SET @strTmpTable = CONCAT('CREATE TEMPORARY TABLE __attendance_temp(',@strTmpTable,') ');
    PREPARE stmt1 FROM @strTmpTable;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
    SET SESSION group_concat_max_len = 10240;
    SET @sumSql = NULL;
--     SELECT * FROM __attendance_temp;
    SELECT GROUP_CONCAT(DISTINCT CONCAT('SUM(IF(dtime = ''',dtime,''',clockIn, "")) AS ''',dtime, '''')) INTO @sumSql FROM __attendance_temp;
--     SELECT @sumSql;
    IF(@sumSql IS NOT NULL) THEN
        SET @strSql = CONCAT('SELECT DISTINCT perName AS "时间", ', @sumSql, ' From __attendance_temp WHERE ISNULL(perName)=0 GROUP BY perName');
    END IF;
--     SELECT @strSql; 
    PREPARE stmt2 FROM @strSql;
    EXECUTE stmt2;
    DEALLOCATE PREPARE stmt2;

END

img