多表关联查询的实例

多表关联查询的实例zby1多表关联查询的实例日常工作中经常会碰到多表的关联查寻，比如用户、角色、权限之间的关系等。1.问题描述：学生（student）、课程（course）、成绩（score）之间的关联查询：2.数据准备#创建数据库CREATEDATABASEIFNOTEXISTStestjoin;#切换数据库USEtestjoin;#创建学生表CREATETABLEIFNOTEXISTSstudent(idINTAUTO_INCREMENTPRIMARYKEY,NAMEVARCHAR(40));#创建课程表CREATETABLEIFNOTEXISTScourse(idINTAUTO_INCREMENTPRIMARYKEY,NAMEVARCHAR(30));#创建成绩表，虽然sid与cid没有用外键，但是事实的外键，这样的好处是在插入时不需要检查外键的约束CREATETABLEIFNOTEXISTSscore(sidINT,cidINT,scoreFLOAT);#插入数据INSERTINTOstudent(NAME)VALUES('小新'),('小可'),('琪琪'),('小萱');INSERTINTOcourse(NAME)VALUES('语文'),('数学'),('历史'),('化学'),('物理'),('地理');INSERTINTOscoreVALUES(1,1,92),(1,2,85),(1,3,90),(1,4,95),(1,5,80);INSERTINTOscoreVALUES(2,1,88),(2,2,92),(2,3,94),(2,4,95),(2,5,88);INSERTINTOscoreVALUES(3,1,95),(3,2,82),(3,3,96),(3,4,91),(3,5,86);3.查询分析查找每个学生最好的一门课程的成绩（包括课程信息、学生信息）#编号、姓名、课程、分数查找多表关联查询的实例zby2SELECTs.id编号,s.name姓名,c.name课程,sc.score分数FROMstudentsINNERJOINscorescONsc.sid=s.idINNERJOINcoursecONc.id=sc.cid;#查找每个学生的最好成绩SELECTsc.sid,MAX(sc.score)mscFROMscorescGROUPBYsc.sid;#查找每个学生的最好成绩以及对应的课程编号SELECTsc1.*FROM(SELECTsc.sid,MAX(sc.score)mscFROMscorescGROUPBYsc.sid)sc2INNERJOINscoresc1ONsc2.sid=sc1.sidANDsc2.msc=sc1.score;#查找每个学生的最好成绩以及对应的课程名、学生信息SELECTs.id编号,s.name姓名,c.name课程,m.score分数FROMstudentsINNERJOIN(SELECTsc1.*FROM(SELECTsc.sid,MAX(sc.score)mscFROMscorescGROUPBYsc.sid)sc2INNERJOINscoresc1ONsc2.sid=sc1.sidANDsc2.msc=sc1.score)mONm.sid=s.idINNERJOINcoursecONm.cid=c.idORDERBYs.idASC;#虽然小萱没有成绩，但也应该显示，通过left[outer]join实现多表关联查询的实例zby3SELECTs.id编号,s.name姓名,c.name课程,m.score分数FROMstudentsLEFTJOIN(SELECTsc1.*FROM(SELECTsc.sid,MAX(sc.score)mscFROMscorescGROUPBYsc.sid)sc2INNERJOINscoresc1ONsc2.sid=sc1.sidANDsc2.msc=sc1.score)mONm.sid=s.idLEFTJOINcoursecONm.cid=c.idORDERBYs.idASC;查找每门课程最好成绩的学生（包括学生信息、课程信息），其道理与上述的例子是一样的。也是通过分解步骤来完成。#查找每门课程的最好成绩SELECTsc.cid,MAX(sc.score)FROMscorescGROUPBYsc.cid;#查找每门课程的最好成绩以及对应的学生编号SELECTsc.*FROMscorescINNERJOIN(SELECTsc.cid,MAX(sc.score)mscFROMscorescGROUPBYsc.cid)sc1ONsc1.cid=sc.cidANDsc1.msc=sc.score;#查找每门课程的最好成绩以及对应的学生姓名、课程信息SELECTc.id编号,c.name课程,s.name姓名,m.score分数FROMstudentsINNERJOIN(SELECTsc.*FROMscorescINNERJOIN(SELECTsc.cid,MAX(sc.score)mscFROMscorescGROUPBYsc.cid)sc1ONsc1.cid=sc.cidANDsc1.msc=sc.score)mONm.sid=s.idINNERJOINcoursecONm.cid=c.id;多表关联查询的实例zby4#地理成绩还没出来，但也得显示，可以通过right[outer]join实现SELECTc.id编号,c.name课程,s.name姓名,m.score分数FROMstudentsRIGHTJOIN(SELECTsc.*FROMscorescINNERJOIN(SELECTsc.cid,MAX(sc.score)mscFROMscorescGROUPBYsc.cid)sc1ONsc1.cid=sc.cidANDsc1.msc=sc.score)mONm.sid=s.idRIGHTJOINcoursecONm.cid=c.id;4.总结对于复杂的关联查询，建议分而治之，即将大目标分小目标，然后通过关联查询实现原定的目标。