mysql-sql-第十周

学习目标：

sql

学习内容：

31.查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
联合查询不会显示没选课的学生（上接）

Select students.stunm,name,u.s,u.t from students ,(select stunm,sum(counm) s,sum(tscore) t from score group by stunm) u where u.stunm=students.stunm

需要显示没选课学生(显示为NULL)，需要使用join
select s.stunm, s.name,r.u,r.t from ((select students.stunm,students.name from students )s left join (select sc.stunm, sum(sc.tscore) as u, count(sc.counm) as t from score sc group by sc.stunm)r on s.stunm = r.stunm);

32.查询" 2001 “课程比” 2002 "课程成绩高的学生的信息及课程分数
因为需要全部的学生信息，则需要在score表中得到符合条件的stunm后与students表进行join，可以left join 也可以 right join

CREATE TEMPORARY TABLE temp1 AS select stunm,tscore from score where counm=‘2001’

CREATE TEMPORARY TABLE temp2 AS select stunm,tscore from score where counm=‘2002’

CREATE TEMPORARY TABLE temp3 AS select a.stunm,a.tscore,b.tscore from temp1 a , temp2 b where a.stunm=b.stunm and a.tscore>b.tscore

ERROR 1060 (42S21): Duplicate column name ‘tscore’

CREATE TEMPORARY TABLE temp4 AS select stunm,tscore v from score where counm=‘2002’

CREATE TEMPORARY TABLE temp3 AS select a.stunm,a.tscore,b.v from temp1 a , temp4 b where a.stunm=b.stunm and a.tscore > b.v

Select * from temp3 left join students on temp3 .stunm=students.stunm

Select*from(select a.stunm,n,m from(select stunm,tscore m from score where counm=‘2001’) a ,(select stunm,tscore n from score where counm=‘2002’) b where a.stunm=b.stunm and a.m>b.n)c left join students on c.stunm=students.stunm

Select*from students right join (select a.stunm,ts1,ts2 from(select stunm,tscore as ts1 from score where score.counm=‘2001’ ) as a,(select stunm ,tscore as ts2 from score where score.counm=‘2002’) as b where a.stunm=b.stunm and a.ts1>b.ts2 ) c on students.stunm=c.stunm

学习时间：

1月-3月，每天一小时左右

学习产出：

一周一发