mysql多对多查询

基础概念

MySQL中的多对多关系是指两个表中的数据项之间存在多个关联关系。例如，学生和课程之间的关系，一个学生可以选修多门课程，而一门课程也可以被多个学生选修。

类型

多对多关系通常通过一个中间表来实现，这个中间表包含两个外键，分别指向两个相关表的主键。

应用场景

学生和课程：学生可以选修多门课程，课程可以被多个学生选修。
用户和角色：一个用户可以拥有多个角色，一个角色也可以分配给多个用户。
产品和类别：一个产品可以属于多个类别，一个类别也可以包含多个产品。

查询示例

假设有三个表：

students（学生表）
courses（课程表）
student_courses（中间表，包含学生ID和课程ID）

表结构

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

CREATE TABLE courses (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

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

查询学生选修的所有课程

SELECT s.name AS student_name, c.name AS course_name
FROM students s
JOIN student_courses sc ON s.id = sc.student_id
JOIN courses c ON sc.course_id = c.id
WHERE s.id = ?;

查询选修某门课程的所有学生

SELECT s.name AS student_name, c.name AS course_name
FROM courses c
JOIN student_courses sc ON c.id = sc.course_id
JOIN students s ON sc.student_id = s.id
WHERE c.id = ?;

遇到的问题及解决方法

问题：查询结果重复

原因：由于多对多关系的中间表可能包含重复记录，导致查询结果出现重复。

解决方法：使用DISTINCT关键字去除重复记录。

SELECT DISTINCT s.name AS student_name, c.name AS course_name
FROM students s
JOIN student_courses sc ON s.id = sc.student_id
JOIN courses c ON sc.course_id = c.id
WHERE s.id = ?;