MySQL控制台执行月度考勤透视表查询报错，求正确实现代码

错误排查 #

现有代码存在以下几个问题导致执行失败或结果不符合预期：

• 动态SQL拼接语法错误：ca.as_guid字段后缺少逗号，与后续的透视列拼接后出现语法冲突，这是直接触发报错的核心原因
• 表关联逻辑缺失：左连接考勤表时仅关联了as_guid，没有匹配日期，会产生笛卡尔积，导致考勤统计错误
• 分组逻辑错误：GROUP BY字段仅写了p.ta_tchr_id，和SELECT中返回的非聚合字段不匹配，在开启ONLY_FULL_GROUP_BY默认规则的MySQL版本中会直接报错
• 考勤状态逻辑缺失：CASE判断中没有加ELSE 'A'，无考勤记录的日期会返回NULL，而非预期的缺席标识'A'
• 列名不符合需求：现有代码用完整日期作为透视列名，需求为当月日期的数字作为列名

正确实现代码 #

-- 生成8月日历临时表
CREATE TEMPORARY TABLE IF NOT EXISTS tmpCalendar AS (
    SELECT * FROM (
        SELECT adddate('1970-01-01', t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date 
        FROM
            (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
            (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
            (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
            (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
            (SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4
    ) v
    WHERE gen_date BETWEEN '2021-08-01' AND '2021-08-31'
);
    
SET SESSION group_concat_max_len = 1000000; 
SET @sql = NULL;

-- 生成动态透视列SQL，列名使用当月日期数字，无考勤默认返回'A'
SELECT GROUP_CONCAT(
    DISTINCT CONCAT(
        'MAX(CASE WHEN ca.gen_date = ''', DATE_FORMAT(gen_date, '%Y-%m-%d'), ''' THEN ''P'' ELSE ''A'' END) AS `', DAY(gen_date), '`'
    )
) INTO @sql
FROM tmpCalendar
WHERE gen_date BETWEEN '2021-08-01' AND '2021-08-31';

-- 拼接完整查询SQL
SET @sql = CONCAT(
    'SELECT ca.as_apts_name, ca.as_apts_nature, ca.as_guid, ', 
    @sql, 
    ' FROM (
        SELECT c.gen_date, a.as_apts_name, a.as_apts_nature, a.as_guid
        FROM tmpCalendar c
        CROSS JOIN tbl_tnts_as_apts a
    ) ca
    LEFT JOIN tblteacheratten_sam p
        ON ca.as_guid = p.ta_as_guid 
        AND ca.gen_date = p.ta_date
    WHERE ca.gen_date BETWEEN ''2021-08-01'' AND ''2021-08-31''
    GROUP BY ca.as_apts_name, ca.as_apts_nature, ca.as_guid'
);

-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

效果说明 #

执行后返回的结果完全符合预期：

• 每行对应一个员工，展示姓名、员工性质
• 列从1到31对应8月每天，当天有考勤记录显示'P'，无记录显示'A'

内容的提问来源于stack exchange，提问作者JSN