MySQL中如何按条件合并行并统计总出勤次数？

原SQL存在的问题 #

• 语法错误：a.original_classification,,a.consent_type中有多余逗号，会导致SQL执行失败。
• 仅完成数据集拆分，未实现行合并与出勤次数统计逻辑。

核心实现思路 #

要完成“将出勤次数较少的行合并到出勤次数较多的行中”的需求，需两步核心操作：

1. 按student_id+school_id+name分组，定位每组内出勤次数最多的行，保留该行的业务字段（如grant、classification等）。
2. 对同组所有符合条件的行，聚合统计总出勤次数。

方案一：MySQL 8.0+（支持窗口函数） #

假设表中表示出勤次数的字段为attendance_count，请替换为实际字段名：

WITH ranked_students AS (
    SELECT 
        *,
        -- 按学生分组，出勤次数降序排名，rn=1对应出勤最多的行
        ROW_NUMBER() OVER (
            PARTITION BY student_id, school_id, name 
            ORDER BY attendance_count DESC
        ) AS rn
    FROM school_temp
    -- 过滤符合需求的行（与原SQL拆分条件一致）
    WHERE (original_classification='all' AND availability='implicit') 
       OR (original_classification!='all' AND availability!='implicit')
)
SELECT 
    rs.student_id,
    rs.school_id,
    rs.name,
    rs.grant,
    rs.classification,
    rs.original_classification,
    rs.consent_type,
    SUM(st.attendance_count) AS total_attendance -- 统计总出勤次数
FROM ranked_students rs
JOIN school_temp st 
    ON rs.student_id = st.student_id 
    AND rs.school_id = st.school_id 
    AND rs.name = st.name
WHERE rs.rn = 1 -- 保留出勤次数最多的行的字段
AND ((st.original_classification='all' AND st.availability='implicit') 
     OR (st.original_classification!='all' AND st.availability!='implicit'))
GROUP BY 
    rs.student_id,
    rs.school_id,
    rs.name,
    rs.grant,
    rs.classification,
    rs.original_classification,
    rs.consent_type;

补充说明

• 若同一分组内有多行出勤次数相同且均为最大值，ROW_NUMBER()会随机选取一行；若需保留所有最大值行，可替换为RANK()或DENSE_RANK()。
• MySQL 5.7+默认开启ONLY_FULL_GROUP_BY，需确保GROUP BY子句包含所有非聚合字段。

方案二：MySQL 5.7及以下版本（无窗口函数） #

SELECT 
    rs.student_id,
    rs.school_id,
    rs.name,
    rs.grant,
    rs.classification,
    rs.original_classification,
    rs.consent_type,
    SUM(st.attendance_count) AS total_attendance
FROM (
    SELECT 
        st1.*,
        -- 子查询实现排名：统计当前行之后有多少行出勤次数更高，+1得到排名
        (SELECT COUNT(*) 
         FROM school_temp st2 
         WHERE st2.student_id = st1.student_id 
           AND st2.school_id = st1.school_id 
           AND st2.name = st1.name 
           AND st2.attendance_count > st1.attendance_count) + 1 AS rn
    FROM school_temp st1
    WHERE (original_classification='all' AND availability='implicit') 
       OR (original_classification!='all' AND availability!='implicit')
) rs
JOIN school_temp st 
    ON rs.student_id = st.student_id 
    AND rs.school_id = st.school_id 
    AND rs.name = st.name
WHERE rs.rn = 1
AND ((st.original_classification='all' AND st.availability='implicit') 
     OR (st.original_classification!='all' AND st.availability!='implicit'))
GROUP BY 
    rs.student_id,
    rs.school_id,
    rs.name,
    rs.grant,
    rs.classification,
    rs.original_classification,
    rs.consent_type;

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