MySQL分组查询：如何在结果中添加每组的记录数量？

我需要根据user_id对数据表中的记录进行分组。以下SQL语句可正常运行，但我希望在结果中添加每个分组的记录数量（对应预期结果中的分组记录数列）。

现有SQL语句 #

SELECT u.user_id, 
        CONCAT(u.first_name, ' ', u.last_name) AS full_name, 
        u.salary,
        n.attendance_group AS month
FROM users AS u
    JOIN (
        SELECT user_id, FLOOR((attendance_count - 1) / 4) + 1 AS max_attendance_group
        FROM (
            SELECT user_id, COUNT(*) AS attendance_count
            FROM attendance
            WHERE is_present = ?
            GROUP BY user_id
        ) AS a
    ) AS b
ON u.user_id = b.user_id
CROSS JOIN (
   SELECT 1 AS attendance_group UNION ALL
   SELECT 2 UNION ALL
   SELECT 3 UNION ALL
   SELECT 4 UNION ALL
   SELECT 5
) AS n
WHERE n.attendance_group <= b.max_attendance_group
ORDER BY u.user_id, n.attendance_group;

当前输出结果 #

预期输出结果 #

解决方案 #

要添加分组记录数列，需要在子查询中保留用户的总出勤记录数，再通过条件判断计算每个分组的实际记录数：

SELECT u.user_id, 
        CONCAT(u.first_name, ' ', u.last_name) AS full_name, 
        u.salary,
        n.attendance_group AS month,
        -- 计算当前分组的记录数
        CASE
            -- 最后一个分组：取总记录数对4取余，余数为0则是4
            WHEN n.attendance_group = b.max_attendance_group THEN 
                IF(a.attendance_count % 4 = 0, 4, a.attendance_count % 4)
            -- 非最后一个分组固定为4
            ELSE 4
        END AS 分组记录数
FROM users AS u
    JOIN (
        SELECT user_id, 
               attendance_count,
               FLOOR((attendance_count - 1) / 4) + 1 AS max_attendance_group
        FROM (
            SELECT user_id, COUNT(*) AS attendance_count
            FROM attendance
            WHERE is_present = ?
            GROUP BY user_id
        ) AS a
    ) AS b ON u.user_id = b.user_id
CROSS JOIN (
   SELECT 1 AS attendance_group UNION ALL
   SELECT 2 UNION ALL
   SELECT 3 UNION ALL
   SELECT 4 UNION ALL
   SELECT 5
) AS n
WHERE n.attendance_group <= b.max_attendance_group
ORDER BY u.user_id, n.attendance_group;

关键修改说明 #

1. 在子查询b中新增attendance_count字段，将最内层子查询的总记录数传递上来，用于后续计算。
2. 用CASE语句区分分组类型：
   • 最后一个分组根据总记录数的余数确定数量，避免出现余数为0时显示0的情况；
   • 前面的分组固定为4条记录。

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