mysql题目实战2

select stu.sid,stu.sname,sc1.cid,sc1.score,tmp.avg_score
from student stu
inner join(
    -- 因为是按sid作为分组，而这个分组只有唯一的一个平均分，因此可以sid和avg(score)同时出现
  select sid,avg(score) as avg_score
  from sc
  group by sid
  ) as tmp
on stu.sid = tmp.sid
inner join sc sc1 
on sc1.sid = stu.sid
where sc1.score >= tmp.avg_score;

select student.*
from sc
inner join student
on sc.sid = student.sid
-- group by 可以进行去重复
group by sc.sid;

select student.*
from (
-- 先把有成绩的学生信息通过group by进行去重，得到的sid都是有成绩的学生id
  select sid from sc group by sid
) sc_tmp 
left join Student
on sc_tmp.sid = student.sid

-- 已选课程的同学
select s.sid,s.sname,COUNT(s.sid) as total_count,SUM(c.score) as total_score
from student s
inner join sc c
on s.sid = c.sid
group by s.sid
union
-- 没选课程的同学
select s.sid,s.sname,0 as total_count, null as total_score
from student s
where s.sid not in( select distinct sc.sid from sc)

select stu.sid,stu.sname,tmp.total_count,tmp.total_score
from Student stu
left join(
  -- 对sc表先按sid进行聚合，第二步用student和聚合的结果进行左连接
  select sid, count(sid) as total_count,sum(score) as total_score
  from sc
  group by sc.sid) tmp
on stu.sid = tmp.sid;

select s.*
from sc c
inner join student s
on c.sid = s.sid
-- 不增加group by会有很多重复的结果
group by s.sid;

select s.*
from(
  select sid
  from sc
  group by sid) tmp
left join student s
on tmp.sid = s.sid;

select count(*) as 李姓老师数量
from Teacher t
where t.Tname like '李%'

-- 找到学生信息
select student.*
from student
where student.sid in(
  -- 找到选修了张三老师教授课的学生编号
  select sc.sid
  from sc
  where sc.cid in(
    -- 找到张三老师教的课程编号
    select course.cid
    from Course
    where course.tid in(
      -- 找到张三老师的教师编号
      select tid
      from Teacher
      where tname = '张三'
    )
  )
);

-- 选了课程但没有选全的
select student.*
from student
inner join(
  select sc.sid,count(sc.sid) as cnt 
  from sc
  group by sc.sid
  -- 这里要用having来过滤而不能用where过滤
  having cnt != (select count(*) as cnt from course)
) tmp 
on student.sid = tmp.sid
union
-- 一门课程都没选的
select student.*
from student
where student.sid not in(
  select sid from sc group by sid
)

select student.*
from student
where student.sid in(
  select sc.sid
  from sc
  where sc.cid in(
    select distinct sc.cid
    from sc
    where sc.sid = '01'
  ) and sc.sid != '01'
)