SQL71 牛客每个人最近的登录日期(六)
select distinct u.name, pn.date, sum(pn.number) over(partition by u.name order by pn.date) as ps_numfrom passing_number pnleft join user u on pn.user_id = u.idorder by pn.date, u.name
解题思路：sum()：组内累加
SQL72 考试分数(一)
select job, round(sum(score) * 1.0 / count(score),3) as avg from grade group by job order by avg desc
select job, round(avg(score),3) as avg from grade group by job order by avg desc
解题思路：求平均值后四舍五入 round(avg(列名),要保留多少位数),按job分组，按avg(score)降序
SQL73 考试分数(二)
select id, a.job, score from grade g left join ( select job, avg(score) as avg from grade group by job ) a on a.job = g.job where g.score > a.avg
select id, job, score from ( select id, job, score, avg(score) over(partition by job) as avg from grade ) a where a.score > a.avg
解题思路：窗口函数avg() over (partition by) 增加一列各类的平均值
SQL74 考试分数(三)
select a.id, a.name, a.score from ( select g.id, l.name, g.score, dense_rank() over(partition by l.name order by g.score desc) as `rank` from grade g left join language l on g.language_id = l.id ) a where a.`rank` <=2
解题思路：主要是用了窗口函数 DENSE_RANK（）
SQL75 考试分数(四)
select job, round(count(id) / 2), round((count(id) + 1) / 2) from grade group by job order by job
解题思路：总数/2 (总数+1)除以2
