SQL27 窗口函数的基础应用
每类试卷得分前3名
https://www.nowcoder.com/practice/255aa1863fe14aa88694c09ebbc1dbca?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0&sourceUrl=%2Fexam%2Foj%3Fpage%3D1%26tab%3DSQL%25E7%25AF%2587%26topicId%3D240
分析我的稀碎答案：
- 妄图用Limit限制最终数据条数
(1)
select tag as tid,uid,
row_number() over (partition by tag order by tag,max(score) desc,
min(score) desc,er.uid desc) as ranking
from exam_record as er
join examination_info as ei
on er.exam_id=ei.exam_id
group by tid,uid
order by tid,ranking limit 3
(2)
select tag as tid,uid,
row_number() over (partition by tag order by tag,max(score) desc,
min(score) desc,er.uid desc) as ranking
from exam_record as er
join examination_info as ei
on er.exam_id=ei.exam_id
group by tid,uid
order by tid,ranking limit 6
- 妄图用having限制最终数据条数
select tag as tid,uid,
row_number() over (partition by tag order by tag,max(score) desc,
min(score) desc,er.uid desc) as ranking
from exam_record as er
join examination_info as ei
on er.exam_id=ei.exam_id
group by tid,uid
having ranking<=3
会报错：You cannot use the alias 'ranking' of an expression containing a window function in this context.
即由窗口函数得到的变量不可用在having语句中。
提交答案：
select tid,uid,ranking from
(
select tag as tid,uid,
row_number() over (partition by tag order by tag,max(score) desc,
min(score) desc,er.uid desc) as ranking
from exam_record as er
join examination_info as ei
on er.exam_id=ei.exam_id
group by tid,uid
) as q
where ranking<=3
