思路: 获取所有有生物课程的人(学号,成绩) - 临时表 获取所有有物理课程的人(学号,成绩) - 临时表 根据【学号】连接两个临时表: 学号 物理成绩 生物成绩 然后再进行筛选 SELECT a_name,h_name,h_number FROM (select (student.student_name)as a_name, (class.class_name)as w_name, (score.number)as w_number from score left join class on class.class_nid = class_sid left join student on student_sid = student.student_nid where class_name = '温泉') as a LEFT JOIN (select (student.student_name)as b_name, (class.class_name)as h_name, (score.number)as h_number from score left join class on class.class_nid = class_sid left join student on student_sid = student.student_nid where class_name = '化学') as b on a_name = b_name WHERE h_number > w_number
SELECT a_name,avg(w_number) FROM (select (student.student_name)as a_name, (class.class_name)as w_name, (score.number)as w_number from score left join class on class.class_nid = class_sid left join student on student_sid = student.student_nid ) as a GROUP BY a_name having avg(w_number) > 60 分组后对聚合函数进行操作 用having
SELECT * FROM (select (student.student_name)as a_name, (class.class_name)as w_name, (score.number)as w_number,(teacher.teacher_name)as t_name from score left join class on class.class_nid = class_sid left join student on student_sid = student.student_nid left join teacher on class_teacher = teacher.teacher_nid ) as a where t_name = '波多'
思路: 先找到上过老师可的学生 在学生表里面 去掉有过的学生 distinct 不同的 这里是去掉上过老师课重名的学生 select * from student where student_name not in (select distinct student_id from score LEFT JOIN course on course_id = course.course_name WHERE teacher_id = '加藤')
1 思路: 2 先找到所有学过生物和温泉的所有学生, 3 然后通过分组判断来显示 4 select * from 5 (select student_id from score 6 where course_id = '温泉' or course_id = '生物' ) as wq 7 GROUP BY student_id having count(student_id) > 1
1 思路: 2 找到所有成绩 按照学号分组,判断是等于总科目的个数 3 4 select * from 5 (select student_id from score 6 group by student_id 7 having COUNT(student_id) < (select count(course_name) from course) 8 ) as bb
1 思路: 2 先找到瞎子同学上的课有那些, 3 然后链接所有上过瞎子课程的同学, 最后去除瞎子 课程总数少的 同学; 4 5 select student_id from 6 (select (student_id) as ssd,(course_id)as sd from score 7 WHERE student_id = '瞎子' 8 ) as sy 9 left join 10 (SELECT * from score) as bb 11 on sd = bb.course_id 12 where student_id != '瞎子' 13 GROUP BY student_id HAVING count(student_id) > 1