MySQL50-3-第1-5题

-- 方法1
select
	a.*
	,b.s_score as 1_score
	,c.s_score as 2_score
from Student a
join Score b on a.s_id = b.s_id  and b.c_id = '01'   -- 两个表通过学号连接，指定01
left join Score c on a.s_id = c.s_id and c.c_id='02' or c.c_id is NULL -- 指定02，或者c中的c_id直接不存在
-- 为NULL的条件可以不存在，因为左连接中会直接排除c表中不存在的数据，包含NULL
where b.s_score > c.s_score;   -- 判断条件


-- 方法2：直接使用where语句
select
	a.*
	,b.s_score as 1_score
	,c.s_score as 2_score
from Student a, Score b, Score c
where a.s_id=b.s_id   -- 列出全部的条件
and a.s_id=c.s_id
and b.c_id='01'
and c.c_id='02'
and b.s_score > c.s_score;   -- 前者成绩高

-- 方法1：通过连接方式实现
select
	a.*
	,b.s_score as 1_score
	,c.s_score as 2_score
from Student a
left join Score b on a.s_id=b.s_id and b.c_id='01' or b.c_id=NULL   -- 包含NULL的数据
join score c on a.s_id=c.s_id and c.c_id='02'
where b.s_score < c.s_score;

-- 通过where子句实现
select
	a.*
	,b.s_score as 1_score
	,c.s_score as 2_score
from Student a, Score b, Score c
where a.s_id=b.s_id
and a.s_id=c.s_id
and b.c_id='01'
and c.c_id='02'
and b.s_score < c.s_score;   -- 前者比较小

-- 执行顺序：先执行分组，再执行avg平均操作
select
	b.s_id
	,b.s_name
	,round(avg(a.s_score), 2) as avg_score
from Student b
join Score a
on b.s_id = a.s_id
group by b.s_id   -- 分组之后查询每个人的平均成绩
having avg_score >= 60;

-- 附加题：总分超过200分的同学
select
	b.s_id
	,b.s_name
	,round(sum(a.s_score),2) as sum_score    -- sum求和
from Student b
join Score a
on b.s_id=a.s_id
group by b.s_id
having sum_score > 200;

-- 加入一个count()计算条件进行筛选

select
	b.s_id
	,b.s_name
	,round(avg(a.s_score), 2) as avg_score
from Student b
join Score a
on b.s_id = a.s_id
group by b.s_id
having avg_score >= 60 and count(b.s_name)=3;  -- 计数条件

select
	b.s_id
	,b.s_name
	,round(avg(a.s_score), 2) as avg_score   -- round四舍五入函数
from Student b
join Score a
on b.s_id = a.s_id
group by b.s_id   -- 分组之后查询每个人的平均成绩
having avg_score < 60;

select
	a.s_id
	,a.s_name
	,0 as avg_score
from Student a
where a.s_id not in (  -- 学生的学号不在给给定表的学号中
  select distinct s_id -- 查询出全部的学号
  from Score
);

select
	S.s_id
	,S.s_name
	,round(avg(ifnull(C.s_score,0)), 2) as avg_score   -- ifnull 函数：第一个参数存在则取它本身，不存在取第二个值0
from Student S
left join Score C
on S.s_id = C.s_id
group by s_id
having avg_score < 60;

select
	a.s_id
	,a.s_name
	,ROUND(AVG(b.s_score), 2) as avg_score
from Student a
left join Score b on a.s_id = b.s_id
GROUP BY a.s_id
HAVING avg_score < 60 or avg_score is null;   -- 最后的NULL判断

select
	a.s_id
	,a.s_name
	,count(b.c_id) as course_number   -- 课程个数
	,sum(b.s_score) as scores_sum  -- 成绩总和
from Student a
left join Score b
on a.s_id = b.s_id
group by a.s_id,a.s_name;