【MySQL】【牛客-SQL进阶挑战】05 窗口函数 持续更新中.....

---

SQL题解汇总

https://blog.csdn.net/guliguliguliguli/article/details/126109166

题目链接

https://www.nowcoder.com/exam/oj?page=1&tab=SQL%E7%AF%87&topicId=240

专用窗口函数

SQL 136 每类试卷得分前3名

WITH t AS(
	SELECT
		tid,
		uid,
		min_score,
		max_score,
		ROW_NUMBER() OVER(PARTITION BY tid ORDER BY max_score DESC,min_score DESC,uid DESC) ranking
	FROM (
		SELECT 
			t2.tag tid,
			t1.uid,
			MIN(t1.score) min_score,
			MAX(t1.score) max_score
		FROM exam_record t1
		JOIN examination_info t2
		ON t1.exam_id = t2.exam_id
		GROUP BY t1.uid, t2.tag
	) temp
)

SELECT tid,uid,ranking
FROM t
WHERE ranking <= 3;

简化一下

WITH t AS(
	SELECT
		tag tid,
		uid,
		ROW_NUMBER() OVER(PARTITION BY tag ORDER BY MAX(score) DESC,MIN(score) DESC,uid DESC) ranking
	FROM exam_record t1
	JOIN examination_info t2
	ON t1.exam_id = t2.exam_id
	GROUP BY t1.uid, t2.tag
)


SELECT tid,uid,ranking
FROM t
WHERE ranking <= 3;

再简化一下

SELECT tid,uid,ranking
FROM (
	SELECT
		tag tid,
		uid,
		ROW_NUMBER() OVER(PARTITION BY tag ORDER BY MAX(score) DESC,MIN(score) DESC,uid DESC) ranking
	FROM exam_record t1
	JOIN examination_info t2
	ON t1.exam_id = t2.exam_id
	GROUP BY t1.uid, t2.tag
) t
WHERE ranking <= 3;

SQL 139 近三个月未完成试卷数为0的用户完成情况

SELECT uid,COUNT(score) exam_complete_cnt
FROM(
	SELECT 
		uid,
		score,
		DATE_FORMAT(start_time,'%Y-%m') start_month,
		DENSE_RANK() OVER(PARTITION BY uid ORDER BY DATE_FORMAT(start_time,'%Y-%m') DESC) number
	FROM exam_record
)temp
WHERE number <= 3
GROUP BY uid
HAVING COUNT(uid)=COUNT(score)
ORDER BY exam_complete_cnt DESC,uid DESC;

聚合窗口函数