参考代码如下
SELECT LOCAL_NAME ,
DEPT_DISTINGUISH_NAME ,
SUM(TOTAL_PAY_SUM_R_LDC) TOTAL_PAY_SUM_R_LDC ,
SUM(BASE_PAY_R_LDC) BASE_PAY_R_LDC
FROM PA_SUMMARY_B04
WHERE PA_MONTH='201509'
GROUP BY rollup(DEPT_DISTINGUISH_NAME,LOCAL_NAME)
得到的会是同一个部门区分下的人员分组,每个部门区分最后加一个合计。最后加一个总计。
在这里可以用到排序时,用一个列
ROW_NUMBER() over( order by NLSSORT(PA.DEPT_DISTINGUISH_NAME,'NLS_SORT = SCHINESE_PINYIN_M'), to_number(GET_GLOBAL_NAME(PA.POST_GRADE_NO,'zh')) DESC) NUM
然后排序时中文按字母排序用如下
ORDER BY NLSSORT(A.DEPT_DISTINGUISH_NAME,'NLS_SORT = SCHINESE_PINYIN_M'),
B.NUM,
B.local_name ;