SELECT YEAR, ROW_NUMBER() OVER(PARTITION BY YEAR ORDER BY MONTH) AS MONTH,
DENSE_RANK() OVER(ORDER BY YEAR) || '.' || ROW_NUMBER() OVER(PARTITION BY YEAR ORDER BY MONTH)
FROM TABLE
month 也根据年份分组,递增
SELECT * FROM
(SELECT YEAR,MONTH,COUNT FROM t1 ORDER BY YEAR,MONTH) t
WHERE ROWNUM<=9
MODEL
DIMENSION BY (YEAR,MONTH)
MEASURES (COUNT)
RULES
(COUNT[2003,2]=COUNT[2002,2]+1,
COUNT[2003,3]=COUNT[2002,3]+1,
COUNT[2003,4]=COUNT[2002,4]+1)
ORDER BY YEAR,MONTH