MySQL多对多关系下按科目ID分组获取所有成绩记录

现有表结构 #

grades表（成绩记录） #

• 主键：id
• st_id关联学生表，同一学生可有多条成绩记录

subjects表（科目信息） #

• 主键：id

grades_subject表（关联表） #

• 联合主键：(sbj_id, st_id, grd_id)
• sbj_id关联subjects.id，st_id关联学生表，grd_id关联grades.st_id

需求 #

获取grades表所有记录，按sbj_id分组展示（每个科目ID下显示对应学生的所有成绩），但现有查询在移除WHERE sbj_id=xxx条件后会出现重复数据。

现有问题查询语句 #

SELECT grades.*, grades_subject.sbj_id
FROM grades
JOIN grades_subject ON grades.st_id = grades_subject.grd_id
where grades_subject.sbj_id = 49
group by grades_subject.sbj_id, grades_subject.grd_id

该语句指定科目时正常，但移除WHERE后，因grades.st_id与grades_subject.grd_id关联，同一学生的多条成绩会和该学生关联的所有科目记录匹配，导致成绩重复显示。

解决方案 #

方案1：不修改表结构的查询方式 #

如果业务逻辑是学生的所有成绩都属于其关联的科目，可通过以下方式处理：

方式1：获取所有不重复的成绩-科目关联记录

SELECT DISTINCT g.*, gs.sbj_id
FROM grades g
JOIN grades_subject gs ON g.st_id = gs.st_id
ORDER BY gs.sbj_id, g.st_id, g.id;

方式2：按科目聚合学生成绩（合并同一学生的多条成绩）

SELECT gs.sbj_id, s.subject, g.st_id, 
       GROUP_CONCAT(CONCAT('Q1:', g.Q1, ', Q2:', g.Q2) ORDER BY g.id SEPARATOR '; ') AS scores
FROM grades g
JOIN grades_subject gs ON g.st_id = gs.st_id
JOIN subjects s ON gs.sbj_id = s.id
GROUP BY gs.sbj_id, s.subject, g.st_id
ORDER BY gs.sbj_id, g.st_id;

方案2：修改表结构（最简根治方案） #

原表结构的核心问题是成绩记录与科目无直接关联，仅通过学生间接关联导致一对多匹配产生重复。最简修改方案二选一：

1. 在grades表中添加sbj_id字段，关联subjects.id，让每条成绩直接绑定科目；
2. 修改grades_subject表的关联逻辑：将grd_id改为关联grades.id（而非grades.st_id），并调整联合主键为(sbj_id, grd_id)，实现每条成绩记录与科目的精准关联。

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