1.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select student.*, a. avg_score from student right join
(select sid,avg(score) as avg_score from sc where score < 60 group by sid having count(score)>=2) a
on student.sid = a.sid
第一步 从sc表中筛选出不及格记录 再按照学生id聚合，并用having count筛选出有2条记录以上的学生id
第二步 与student表关联
2.检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select student.*, a.score from student right join (
select * from sc where cid = '01' and score <60) a
on student.sid = a.sid order by a.score desc
双表联合查询写法
select student.*, sc.score from student, sc
where student.sid = sc.sid
and sc.score < 60
and cid = "01"
ORDER BY sc.score DESC;
- 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select sc.score ,a.* from sc left join (
select sid, avg(score) as avg_score from sc group by sid) a
on sc.sid = a.sid order by a.avg_score desc
过程总结一个问题，后面有group by聚合的话，前面select的字段不能为。这是因为聚合之后的汇总数据条数比整体的要少，没法一起select，所以要先把汇总数据写在子查询里，再去与相关的表关联
4.查询各科成绩最高分、最低分和平均分：
select course.cid,course.cname ,a.avg_score, a.max_score, a.min_score from course left join(
select cid,avg(score) as avg_score, max(score) as max_score, min(score) as min_score
from sc group by cid) a
on course.cid = a.cid
与13题思路相同，结果：
15.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
15-1 按各科成绩进行排序，并显示排名， Score 重复时合并名次
不会
SELECT *,RANK() over( partition by cid order by score desc) 排名 FROM SC
SELECT *,DENSE_RANK() over( partition by cid order by score desc) 排名 FROM SC
PS:
over不能单独使用，要和分析函数：rank(),dense_rank(),row_number()等一起使用。
其参数：over（partition by columnname1 order by columnname2）
https://www.cnblogs.com/weihengblogs/p/4475798.html
16.查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
select *, dense_rank() over( order by a.sum_score desc) as 排名 from(
select sc.sid, sum(score) as sum_score from sc group by sid
) a
思路同15题
17.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
select sc.cid,
round(count(case when sc.score<=100 and sc.score>85 then sid else null end) /count(sid) ::numeric,4 )as a,
round(count(case when sc.score<=85 and sc.score>70 then sid else null end) /count(sid) ::numeric,4 ) as b,
round(count(case when sc.score<=70 and sc.score>60 then sid else null end) /count(sid) ::numeric,4 )as c,
round(count(case when sc.score<=60 and sc.score>0 then sid else null end) /count(sid) ::numeric,4 )as d
from sc
group by sc.cid;
postgresql除法默认结果保留整数，需要用::numeric转换类型之后再用round（）函数保留4位小数
18.查询各科成绩前三名的记录
select * from (
select cid,sid,score,row_number() over (partition by cid order by score desc) rank from sc) r
where rank <=3
order by cid,score desc;
思路同15题， 分组之后针对每组进行数据排序再按条件筛选
- 查询每门课程被选修的学生数
select cid, count(sid) from sc group by cid
20.查询出只选修两门课程的学生学号和姓名
select student.sid, student.sname from student right join (
select sid,count(cid) as cls_num from sc group by sid having count(cid)=2) r
on student.sid = r.sid
上面是嵌套查询，如果是联合查询：
select student.SId,student.Sname
from sc,student
where student.SId=sc.SId
GROUP BY sc.SId
HAVING count(*)=2；
