oracle给每行加上序号,Oracle 根据分类字段给查询结果添加序号

一、分类排序

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