union的用法
select a.s_id,a.s_name,round(avg(b.s_score),2) avg_score from
student a
join score b on a.s_id = b.s_id
group by a.s_id
having avg_score < 60
union
select a.s_id,a.s_name, 0 avg_score from
student a
where a.s_id not in (select distinct s_id from score)
利用多张表联系之后筛选
select a.* from student a
where a.s_id not in(
select s_id from score where c_id in(
select c_id from course where t_id in (select t_id from teacher where t_name = '张三')))
结果:
s_id | s_name | s_birth | s_sex |
---|---|---|---|
06 | 吴兰 | 1992-03-01 | 女 |
08 | 王菊 | 1990-01-20 | 女 |
抽取前三名
SELECT a.s_id,a.c_id,a.s_score,b.s_score FROM score a
LEFT JOIN score b on a.c_id = b.c_id and a.s_score<b.s_score
GROUP BY a.s_id,a.c_id,a.s_score HAVING COUNT(b.s_id)<3
ORDER BY a.c_id,a.s_score DESC