一、分类排序
SELECT ROW_NUMBER() OVER(PARTITION BY T.CLASS_NAME ORDER BY T.SCORE) AS IDX, T.* FROM TB_SCORE T;
二、分类逐行统计
SELECT SUM(T.STU_NUM) OVER(PARTITION BY T.CLASS_NAME ORDER BY T.SCORE DESC) AS IDX, T.* FROM TB_SCORE T;
PS：纯SQL实现分类排序
--==============================================
-- 根据分类字段给记录添加递增或递减序号
--============================================
SELECT RN, IDX, YEAR_S, RELEASE_TIME FROM (
-- 获取分类后的序号 IDX
SELECT (
SELECT COUNT(1) FROM (
-- 获取排序序号 RN, 得到临时表二
SELECT T.*, ROWNUM AS RN FROM (
-- 获取分类字段 YEAR_S
SELECT TO_CHAR(T.RELEASE_TIME, 'YYYY') AS YEAR_S, T.* FROM TABLE_NAME T ORDER BY T.RELEASE_TIME
) T
) WHERE YEAR_S=T1.YEAR_S AND RN <= T1.RN -- 重点: 临时表一和临时表二关联, 分类字段相同, 统计表二排序字段小于等于当前记录排序字段的记录数; RN >= T1.RN 则递减
) AS IDX, T1.* FROM (
-- 获取排序序号 RN, 得到临时表一
SELECT T.*, ROWNUM AS RN FROM (
-- 获取分类字段 YEAR_S
SELECT TO_CHAR(T.RELEASE_TIME, 'YYYY') AS YEAR_S, T.* FROM TABLE_NAME T ORDER BY T.RELEASE_TIME
) T
) T1
) WHERE IDX <= 3 ORDER BY RELEASE_TIME