MariaDB 10.4分组函数误用错误排查与考勤数据转换求助

错误原因 #

原SQL的核心问题是嵌套使用聚合函数GROUP_CONCAT：外层按class_id和month_id分组时，内层又对bio_punch_details调用GROUP_CONCAT，而MySQL/MariaDB不允许聚合函数直接嵌套使用，因此触发该错误。

解决方案 #

通过拆分两步聚合来规避嵌套问题：

1. 先按class_id+attendance_date分组，生成单日的考勤记录（包含当日所有打卡记录的JSON数组）；
2. 再按class_id+month_id分组，将单日记录聚合为整月的完整考勤JSON数组。

修正后的SQL #

-- 临时调整GROUP_CONCAT最大长度，避免大数量数据被截断（根据实际业务调整值）
SET SESSION group_concat_max_len = 1000000;

INSERT INTO attendance_student (class_id, month_id, attendance_details, created_at, updated_at)
SELECT 
    class_id,
    month_id,
    CONCAT('[', GROUP_CONCAT(day_record ORDER BY attendance_date ASC SEPARATOR ','), ']') AS attendance_details,
    NOW() AS created_at,
    NOW() AS updated_at
FROM (
    SELECT 
        class_id,
        DATE_FORMAT(attendance_date, '%Y%m') AS month_id,
        attendance_date,
        -- 生成单天的考勤JSON记录，包含当日打卡数组
        CONCAT(
            '{"att_date":"', DATE_FORMAT(attendance_date, '%d-%m-%Y'), '",',
            '"att_status":', attendance_status, ',', -- 匹配期望格式的数字类型，去掉引号
            '"att_punch":',
            IFNULL(
                CONCAT('[', GROUP_CONCAT(
                    CONCAT(
                        '{"did":"', REPLACE(bio_punch_details, '"', '\\"'), '",', -- 转义双引号避免JSON格式错误
                        '"pat":"', IFNULL(DATE_FORMAT(created_at, '%d-%m-%Y %H:%i:%s'), ''), '",',
                        '"ss":"', REPLACE(bio_punch_details, '"', '\\"'), '"}'
                    ) ORDER BY created_at ASC SEPARATOR ','
                ), ']'),
                '[]'
            ),
            '}'
        ) AS day_record
    FROM student_attendance_daily
    GROUP BY class_id, attendance_date -- 先按班级+日期分组，处理单日打卡记录
) AS daily_records
GROUP BY class_id, month_id; -- 再按班级+月份分组，聚合整月考勤数据

关键改进点 #

• 拆分嵌套聚合：通过子查询先完成单日打卡记录的聚合，避免了GROUP_CONCAT嵌套；
• 修正JSON格式：将att_status改为数字类型（匹配期望格式），同时用REPLACE转义bio_punch_details中的双引号，防止JSON结构失效；
• 优化排序逻辑：确保打卡记录按时间排序、单日考勤按日期排序；
• 处理截断问题：临时调整group_concat_max_len，避免大数量场景下的JSON截断。

验证结果 #

执行后生成的attendance_details将完全匹配你提供的期望格式，例如：

[{"att_date":"12-01-2023","att_status":0,"att_punch":[{"did":"1","pat":"12-01-2023 18:35:33","ss":"ss_std_20230112183534_667757.jpg"}]},{"att_date":"20-01-2023","att_status":1,"att_punch":[{"did":"2","pat":"20-01-2023 00:29:48","ss":"ss_std_20230120002949_672689.jpg"}]}]

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