今天看到一条hql题目如下:
核心思想:两个有序字段相减的值相同,小例子如下:
list a = [1,2,4,5,6,7,8,10,11,12] (看作 test_id)
list b = [1,2,3,4,5,6,7,8,9,10] (看作 row_number)
令list c = list a - list b
list c = [0,0,1,1,1,1,1,2,2,2]
问题解决(没有做测试哈,只是整理了思路写HQL,若有误或有更简单的方法可以在博客留言):
1、求出各个test每个学生的总成绩
SELECT test_id,std_id,std_name,
SUM(score) OVER(PARTITION BY test_id,std_id) AS total_score
FROM test_tb
2、对学生总成绩进行排名
SELECT test_id,std_id,std_name,
DENSE_RANK() OVER(PARTITION BY test_id ORDER BY total_score) as d_rank
FROM
(SELECT test_id,std_id,std_name,
SUM(score) OVER(PARTITION BY test_id,std_id) AS total_score
FROM t