分组查询多对多表

分组查询多对多表通常涉及到数据库设计和SQL查询技巧。以下是对这个问题的详细解答：

基础概念

多对多关系：在数据库设计中，多对多关系指的是两个实体集合之间存在的复杂关系，其中一个实体的多个实例可以与另一个实体的多个实例相关联。例如，学生和课程之间的关系，一个学生可以选修多门课程，一门课程也可以被多个学生选修。

中间表（Join Table）：为了实现多对多关系，通常会创建一个中间表，该表包含两个实体的主键作为外键，用于连接这两个实体。

类型与应用场景

类型：

简单多对多：两个实体之间直接的关联。
带属性的多对多：中间表中包含额外的字段来描述关联的属性。

应用场景：

教育系统：学生与课程的关系。
社交网络：用户与兴趣爱好的关系。
电商系统：商品与标签的关系。

示例代码

假设有两个表 students 和 courses，以及一个中间表 student_courses，结构如下：

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    title VARCHAR(100)
);

CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    grade CHAR(1),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

查询每个学生选修的所有课程及其成绩

SELECT s.name AS student_name, c.title AS course_title, sc.grade
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
ORDER BY s.name, c.title;

按学生分组查询其选修课程的平均成绩

SELECT s.name AS student_name, AVG(sc.grade) AS average_grade
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
GROUP BY s.student_id, s.name;