1:需求介绍
表achievement_records 是记录学生是否有作业(同一个学生、同一个作业只有一条记录----是否交了作业,最高成绩)
表achievement 是记录学生每次作业的上交记录(同一个学生,同一个作业可以上交N次-----每次作业都有分数、评语)
期望:achievement_records left join achievement (同一个学生、同一个作业,最后一次作业的分数)
ps:several_times 当前是第几次提交作业
2:看下数据格式
期望:left join这张表数据时,红框内的2条数据,只想关联到several_times最大的一条数据。
3:分析
如何取到指定user_id、homework_id 的 several_times最大的一条数据?
思路1:先对several_times 进行倒叙排序,然后使用分组:
sql 如下:
SELECT *FROM (
SELECT * FROM achievement ORDER BY several_times DESC
) a GROUP BY a.user_id, a.homework_id
思路2:先对user_id、homework_id进行分区,在对several_times 进行降序排序
with cte as (
SELECT *,row_number() OVER(PARTITION BY user_id,homework_id order by several_times desc) rn from achievement where del_flag = 0
) select * from cte where rn=1
结果思路1是错误的。执行结果获取到several_times = 1的数据
思路2满足预期结果
4:联表查询语句
SELECT ar.user_id,ar.homework_id,ar.high_score,a.score,a.several_times
FROM achievement_records ar
LEFT JOIN (
WITH cte AS (
SELECT *,row_number() OVER(PARTITION BY user_id,homework_id ORDER BY several_times DESC) rn FROM achievement WHERE del_flag = 0
) SELECT * FROM cte WHERE rn=1
) a ON a.user_id = ar.user_id AND a.homework_id = ar.homework_id AND a.del_flag = 0
WHERE ar.del_flag = 0
AND ar.user_id = 1381864516219863041
AND ar.homework_id = 1386602729492758530
ps:此文章仅记录工作遇到的问题,并不包含项目或公司敏感信息。