多对多关系的多表关联查询

1.什么是多对多关系

       多对多关系(百度)：多对多关系是关系数据库中两个表之间的一种关系， 该关系中第一个表中的一个行可以与第二个表中的一个或多个行相关。第二个表中的一个行也可以与第一个表中的一个或多个行相关。

       下面我就举个比较好理解的例子来说明这个概念。学生和学生所选的选修课之间的关系，就符合多对多的关系，怎么理解呢？一个学生可能会选择多门选修课，而,一门选修课则可能会对应多个学生，下面我以此为例子来说明这个问题。

2.前期准备

创建物理表，并且初始化测试数据(笔者创建在mysql)

创建学生表(STUDENT)

CREATE TABLE STUDENT(  
   ID   VARCHAR(20) primary key,  
   NAME VARCHAR(20),  
   AGE INT  
); 

创建选修课程表(SUBJECT)

CREATE TABLE SUBJECT(  
   ID   VARCHAR(20) primary key,  
   NAME VARCHAR(40)  
);

创建学生和选修课程的关联表(STU_REF_SUB)

CREATE TABLE STU_REF_SUB(  
   STUDENT_ID VARCHAR(20),  
   SUBJECT_ID VARCHAR(20)  
);

添加外键

ALTER TABLE STU_REF_SUB ADD CONSTRAINT FK_STUDENT FOREIGN KEY(STUDENT_ID) REFERENCES STUDENT(ID);

ALTER TABLE STU_REF_SUB ADD CONSTRAINT FK_SUBJECT FOREIGN KEY(SUBJECT_ID) REFERENCES SUBJECT(ID);

添加联合主键

ALTER TABLE STU_REF_SUB ADD CONSTRAINT PK_STU_REF_SUB PRIMARY KEY(STUDENT_ID,SUBJECT_ID); 

诸表数据初始化

STUDENT表数据初始化

INSERT INTO STUDENT VALUES('STU001','李白',25);  
INSERT INTO STUDENT VALUES('STU002','杜甫',26);  
INSERT INTO STUDENT VALUES('STU003','欧阳修',27);  
INSERT INTO STUDENT VALUES('STU004','岳飞',26);  
INSERT INTO STUDENT VALUES('STU005','柳永',28); 

SUBJECT表数据初始化

INSERT INTO SUBJECT VALUES('SUB001','网球课');  
INSERT INTO SUBJECT VALUES('SUB002','诗词课');  
INSERT INTO SUBJECT VALUES('SUB003','计算机');  
INSERT INTO SUBJECT VALUES('SUB004','乒乓球');  
INSERT INTO SUBJECT VALUES('SUB005','篮球课');

STU_REF_SUB表数据初始化

INSERT INTO STU_REF_SUB VALUES('STU001','SUB001');  
INSERT INTO STU_REF_SUB VALUES('STU001','SUB003');  
INSERT INTO STU_REF_SUB VALUES('STU001','SUB004');  
INSERT INTO STU_REF_SUB VALUES('STU002','SUB002');  
INSERT INTO STU_REF_SUB VALUES('STU002','SUB004');  
INSERT INTO STU_REF_SUB VALUES('STU004','SUB001');  
INSERT INTO STU_REF_SUB VALUES('STU004','SUB005');  
INSERT INTO STU_REF_SUB VALUES('STU005','SUB003'); 

3.场景查询

查询哪些学生选了课程

sql语句(方式一)

select 
    student.name '学生名称',  
    subject.name '课程名称'   
from  
    student,  
    subject,  
    stu_ref_sub    
where   
    student.id = stu_ref_sub.student_id   
and subject.id = stu_ref_sub.subject_id;

(方式一)查询结果如下

竖表转化为横表

select 
    t1.student '学生姓名',
    max(case t1.course when '网球课' then '网球课' else '0' end) '课程一',
    max(case t1.course when '计算机' then '计算机' else '0' end) '课程二',
    max(case t1.course when '篮球课' then '篮球课' else '0' end) '课程三',
    max(case t1.course when '乒乓球' then '乒乓球' else '0' end) '课程四',
    max(case t1.course when '诗词课' then '诗词课' else '0' end) '课程五'
from
  (
   select 
       student.name as student,  
       subject.name as course   
   from  
       student,  
       subject,  
       stu_ref_sub    
   where   
       student.id = stu_ref_sub.student_id   
   and subject.id = stu_ref_sub.subject_id
  ) t1
group by t1.student

竖转横查询结果如下

(方式一)查询小结

       1.在将竖表转化为横表的时候，笔者在这里使用了聚合函数max()，需要注意的是,聚合函数max()不仅可以作用于数值类型的数据，还可以作用于字符串类型数据和日期时间类型的数据。而sum()，只是作用于数值类型数据，用于返回指定数据的和，空值会被默认忽略。