-- 2select A.student_id,A.num as 生物,B.num as 物理 from (select student_id,num,cname from score left join course on course_id = cid where cname = ‘生物‘) as Aleft join(select student_id,num,cname from score left join course on course_id = cid where cname = ‘物理‘) as Bon A.student_id = B.student_id where A.num > if(isnull(B.num),0,B.num )-- 3-- select student_id as 学号,avg(num) as 平均成绩 from score group by student_id having avg(num) >60;-- 4有问题的代码,不知道什么问题select student_id,count(course_id),group_concat(sname) from (select * from score left join student on score.student_id = student.sid) as Agroup by A.student_id 正确代码select student_id,sname,课程数,总成绩 from (select student_id,count(course_id) as 课程数,sum(num) as 总成绩 from score group by student_id) as A left join student on A.student_id = student.sid-- 5-- select count(tid) from teacher where tname like ‘李%‘-- 6select student_id,GROUP_CONCAT(DISTINCT sname),GROUP_CONCAT(DISTINCT tname) from(select * from (select student_id,course_id,num,sname from score left join student on score.student_id = student.sid) as A left join course on A.course_id = course.cid) as Bleft join teacher on B.teacher_id = teacher.tid group by student_id HAVING not FIND_IN_SET(‘李平老师‘,GROUP_CONCAT(DISTINCT tname));-- 7select student_id,sname from (select student_id,GROUP_CONCAT(course_id) as group_course from score group by student_id HAVING FIND_IN_SET(1,group_course) and FIND_IN_SET(2,group_course)) as Aleft JOINstudent on A.student_id = student.sid-- 8-- 方法1,有问题,不能自己写李平的课程是2 4select student_id,GROUP_CONCAT(DISTINCT sname),GROUP_CONCAT( tname),GROUP_CONCAT( DISTINCT course_id) from(select * from (select student_id,course_id,num,sname from score left join student on score.student_id = student.sid) as A left join course on A.course_id = course.cid) as Bleft join teacher on B.teacher_id = teacher.tid group by student_id HAVING FIND_IN_SET(‘2‘,GROUP_CONCAT(DISTINCT course_id)) and FIND_IN_SET(‘4‘,GROUP_CONCAT(DISTINCT course_id)) 方法2SELECT * from(select student_id,GROUP_CONCAT(course_id ORDER BY course_id ASC) as course_student,GROUP_CONCAT(DISTINCT sname),GROUP_CONCAT(DISTINCT tname) as tname from(select * from (select student_id,course_id,num,sname from score left join student on score.student_id = student.sid) as A left join course on A.course_id = course.cid) as Bleft join teacheron B.teacher_id = teacher.tid WHERE tname = ‘李平老师‘ GROUP BY student_id) as Cleft JOIN(select tname,GROUP_CONCAT(cid ORDER BY cid ASC) as course_teacher from course INNER JOIN teacher on course.teacher_id = teacher.tid where tname = ‘李平老师‘ GROUP BY tname) as Don C.tname = D.tname WHERE course_student = course_teacher 方法3select score.student_id,student.sname,count(score.course_id) as 选课总数,GROUP_CONCAT(DISTINCT tc2_tw) as 讲课总数 from score inner join student inner join (select teacher.tname as tc2_tn,course.cid as tc2_tcid,tc.td as tc2_tw from course inner join teacher inner join (select teacher.tname as tn,count(course.cid) as td from course inner join teacher on course.teacher_id = teacher.tid group by tn having tn=‘李平老师‘) as tc on course.teacher_id = teacher.tid and tc.tn = teacher.tname) as tc2 on score.course_id = tc2.tc2_tcid and score.student_id = student.sid group by score.student_id HAVING 选课总数 = 讲课总数 方法三拆分1李平老师教的课程数select teacher.tname as tn,count(course.cid) as td from course inner join teacher on course.teacher_id = teacher.tid group by tn having tn=‘李平老师‘ and tc.tn = teacher.tname方法三拆分2李平老师教的课程号和课程数select teacher.tname as tc2_tn, course.cid as tc2_tcid, tc.td as tc2_tw from course inner join teacher inner join (select teacher.tname as tn,count(course.cid) as td from course inner join teacher on course.teacher_id = teacher.tid group by tn having tn=‘李平老师‘) as tc on course.teacher_id = teacher.tid and tc.tn = teacher.tname-- 9select student_id,sname from (select A.student_id from(SELECT * from score where course_id = 1) as Aleft JOIN(SELECT * from score where course_id = 2) as Bon A.student_id = B.student_id where A.num > B.num) as Cleft JOINstudent on C.student_id = student.sid-- 10SELECT student_id,sname from(select student_id from score where num <60 GROUP BY student_id) as Aleft JOINstudenton A.student_id = student.sid-- 11select student_id,sname from(select * from (SELECT student_id,GROUP_CONCAT(course_id ORDER BY course_id) as 选的课程 from score GROUP BY student_id) as Ainner JOIN(select GROUP_CONCAT(cid ORDER BY cid ASC) as 全部课程 from course) as Bwhere 选的课程 != 全部课程) as Cleft JOINstudent on C.student_id = student.sid-- 12 有问题的代码SELECT score.student_id from(select student_id,GROUP_CONCAT(course_id)as a from score GROUP BY student_id HAVING student_id = 1) as Ainner JOINscore where course_id in (1,2,4) GROUP BY score.student_id-- 这里的a 是字符串 ,而in方法只能用列表,mysql 又不能将字符串转化为列表,不得不说这个mysql是真难用-- where a = ‘1,2,4‘ -- where course_id in (1,2,4)SELECT student_id,sname from(SELECT student_id from(select course_id as A_course from score where student_id = 1) as Ainner JOINscore where course_id = A_course GROUP BY student_id) as BLEFT JOINstudenton B.student_id = student.sid -- 13 不知道是不是审题错了,和12题基本一样SELECT student_id,sname from(SELECT student_id from(select course_id as A_course from score where student_id = 1) as Ainner JOINscore where course_id = A_course GROUP BY student_id) as BLEFT JOINstudenton B.student_id = student.sid where student_id !=1-- 14SELECT student_id,sname from(SELECT student_id from(select student_id as a_id, GROUP_CONCAT(course_id ORDER BY course_id ASC)as a from score GROUP BY student_id HAVING student_id = 2) as Ainner JOIN(select student_id,GROUP_CONCAT(course_id ORDER BY course_id ASC) as b from score GROUP BY student_id) as Bwhere a = b and student_id != 2)as CLEFT JOINstudenton C.student_id = student.sid-- 15不知道这样写为什么不行,难道是主键不能删除delete from score where sid in(select sid from scoreleft join(select * from course left join teacher on course.teacher_id = teacher.tid) as Aon score.course_id = A.cidwhere tname = ‘李平老师‘)delete from score where course_id in ( select cid from course left join teacher on course.teacher_id = teacher.tid where teacher.tname = ‘李平老师‘ ) -- 16INSERT into score(student_id,course_id,num) SELECT student_id,2,AVG(num) from score where student_id not in (select student_id from score where course_id = 2)GROUP BY student_id-- insert into score(student_id, course_id, num) select sid,2,(select avg(num) from score where course_id = 2)-- from student where sid not in (-- select student_id from score where course_id = 2-- )-- 17select A.student_id as 学号,生物,物理,体育,美术,平均分 from(select student_id,num as 生物 from scoreinner JOINcourse on score.course_id = course.cid where cname = ‘生物‘) as Ainner JOIN(select student_id,num as 物理 from scoreinner JOINcourse on score.course_id = course.cid where cname = ‘物理‘) as Binner join(select student_id,num as 体育 from scoreinner JOINcourse on score.course_id = course.cid where cname = ‘体育‘) as Cinner join (select student_id,num as 美术 from scoreinner JOINcourse on score.course_id = course.cid where cname = ‘美术‘) as Dinner join(select student_id,AVG(num) as 平均分 from score GROUP BY student_id) as Eon A.student_id = B.student_id and A.student_id = C.student_id and A.student_id = D.student_id and A.student_id = E.student_id ORDER BY 平均分 ASC-- 18-- select course_id,max(num),min(num) from score group by course_id-- 19SELECT C.course_id,平均分,及格率 from(select course_id,avg(num) as 平均分,GROUP_CONCAT(num) from score group by course_id) as CLEFT JOIN(select A.course_id,及格, 总数,及格/总数 as 及格率 from(SELECT course_id,count(num) as 及格 from score where num >=60 GROUP BY course_id) as Aleft JOIN(SELECT course_id,count(num) as 总数 from score GROUP BY course_id) as Bon A.course_id = B.course_id) as Don C.course_id = D.course_id ORDER BY 平均分 ASC, 及格率 DESC-- 20select 平均分,tname from (select course_id,avg(num) as 平均分 from score GROUP BY course_id ORDER BY 平均分 DESC ) as ALEFT JOIN(select * from course LEFT JOIN teacher on course.teacher_id = teacher.tid ) as Bon A.course_id = B.cid ORDER BY 平均分 DESC-- 21-- 还没做出来,有问题-- select course_id,GROUP_CONCAT(num),GROUP_CONCAT(student_id ORDER BY num DESC ) from score GROUP BY course_id-- 22 -- select course_id,COUNT(student_id) from score GROUP BY course_id-- 23select A.student_id,sname from (select student_id from score GROUP BY student_id HAVING count(course_id) = 1) as ALEFT JOINstudenton A.student_id = student.sid-- 24 方法一select * from(select count(1) as man from student where gender = ‘男‘) as A,(select count(1) as feman from student where gender = ‘女‘) as B-- 方法二 -- select gender,count(1) from student group by gender-- 25-- select * from student where sname like ‘张%‘-- 26-- select sname,count(1) from student GROUP BY sname -- 27-- select course_id,avg(if(isnull(num), 0 ,num)) as avg from score GROUP BY course_id ORDER BY avg ASC,course_id DESC-- 28SELECT student_id,sname,avg from(select student_id,avg(if(isnull(num),0,num)) as avg from score GROUP BY student_id HAVING avg > 85) as ALEFT JOINstudent on A.student_id = student.sid-- 29select student_id,sname,num from(select * from score left JOINcourse on score.course_id = course.cid where cname = ‘生物‘ and num < 60) as Aleft join student on A.student_id = student.sid-- 30SELECT A.student_id,sname from(select student_id from score where course_id = 3 and num >80) as ALEFT JOINstudent on A.student_id = student.sid-- 31select count(1) from(select student_id,count(1) from score GROUP BY student_id) as A-- 32select student_id,sname from (select student_id,num from (select * from score LEFT JOINcourse on score.course_id = course.cid) as ALEFT JOINteacher on A.teacher_id = teacher.tid where tname = ‘刘海燕老师‘ ORDER BY num desc LIMIT 1) as BLEFT JOINstudent on B.student_id = student.sid-- 33select cname,count from (select course_id,count(1) as count from score GROUP BY course_id) as Aleft join course on A.course_id = course.cid-- 34-- select DISTINCT s1.course_id,s2.course_id,s1.num,s2.num from score as s1, score as s2 where s1.num = s2.num and s1.course_id != s2.course_id;-- 35-- 有问题-- select course_id,GROUP_CONCAT(sid ORDER BY num desc) from score GROUP BY course_id -- 36-- select student_id from score GROUP BY student_id HAVING count(course_id) > 1-- 37select course_id,GROUP_CONCAT(student_id ORDER BY student_id) as st from score GROUP BY course_id HAVING st=(select GROUP_CONCAT( DISTINCT student_id ORDER BY student_id) from score )-- 38SELECT A.student_id,sname from (select * from score where student_id not in(select DISTINCT student_id from scoreINNER JOINcourseINNER JOINteacher on score.course_id = course.cid AND course.teacher_id = teacher.tid WHERE tname = ‘李平老师‘)) as ALEFT JOINstudent on A.student_id = student.sid-- 39select A.student_id,avg from (select student_id ,count(1) as count from score where num<60 GROUP BY student_id HAVING count >1) as ALEFT JOIN(select student_id,avg(num) as avg from score GROUP BY student_id) as B on A.student_id = B.student_id-- 40-- SELECT student_id from score where course_id= 4 and num < 60 ORDER BY num desc -- 41-- delete from score where course_id = 1 and student_id = 221 35题 一个问题,没有解决,没做出来