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

出处：https://blog.csdn.net/liubin5620/article/details/78617895

1.什么是多对多关系

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

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

2.前期准备

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

创建学生表(STUDENT)

1. CREATE TABLE STUDENT(

2. ID VARCHAR(20) primary key,

3. NAME VARCHAR(20),

4. AGE INT

5. );

创建选修课程表(SUBJECT)

1. CREATE TABLE SUBJECT(

2. ID VARCHAR(20) primary key,

3. NAME VARCHAR(40)

4. );

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

1. CREATE TABLE STU_REF_SUB(

2. STUDENT_ID VARCHAR(20),

3. SUBJECT_ID VARCHAR(20)

4. );

添加外键

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表数据初始化

1. INSERT INTO STUDENT VALUES('STU001','李白',25);

2. INSERT INTO STUDENT VALUES('STU002','杜甫',26);

3. INSERT INTO STUDENT VALUES('STU003','欧阳修',27);

4. INSERT INTO STUDENT VALUES('STU004','岳飞',26);

5. INSERT INTO STUDENT VALUES('STU005','柳永',28);

SUBJECT表数据初始化

1. INSERT INTO SUBJECT VALUES('SUB001','网球课');

2. INSERT INTO SUBJECT VALUES('SUB002','诗词课');

3. INSERT INTO SUBJECT VALUES('SUB003','计算机');

4. INSERT INTO SUBJECT VALUES('SUB004','乒乓球');

5. INSERT INTO SUBJECT VALUES('SUB005','篮球课');

STU_REF_SUB表数据初始化

1. INSERT INTO STU_REF_SUB VALUES('STU001','SUB001');

2. INSERT INTO STU_REF_SUB VALUES('STU001','SUB003');

3. INSERT INTO STU_REF_SUB VALUES('STU001','SUB004');

4. INSERT INTO STU_REF_SUB VALUES('STU002','SUB002');

5. INSERT INTO STU_REF_SUB VALUES('STU002','SUB004');

6. INSERT INTO STU_REF_SUB VALUES('STU004','SUB001');

7. INSERT INTO STU_REF_SUB VALUES('STU004','SUB005');

8. INSERT INTO STU_REF_SUB VALUES('STU005','SUB003');

3.场景查询

查询哪些学生选了课程

sql语句(方式一)

1. select

2. student.name '学生名称',

3. subject.name '课程名称'

4. from

5. student,

6. subject,

7. stu_ref_sub

8. where

9. student.id = stu_ref_sub.student_id

10. and subject.id = stu_ref_sub.subject_id;

(方式一)查询结果如下

竖表转化为横表

1. select

2. t1.student '学生姓名',

3. max(case t1.course when '网球课' then '网球课' else '0' end) '课程一',

4. max(case t1.course when '计算机' then '计算机' else '0' end) '课程二',

5. max(case t1.course when '篮球课' then '篮球课' else '0' end) '课程三',

6. max(case t1.course when '乒乓球' then '乒乓球' else '0' end) '课程四',

7. max(case t1.course when '诗词课' then '诗词课' else '0' end) '课程五'

8. from

9. (

10. select

11. student.name as student,

12. subject.name as course

13. from

14. student,

15. subject,

16. stu_ref_sub

17. where

18. student.id = stu_ref_sub.student_id

19. and subject.id = stu_ref_sub.subject_id

20. ) t1

21. group by t1.student

竖转横查询结果如下

(方式一)查询小结

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