Oracle分组统计小计放在最后一行的处理
在做统计查询,需要一个小计百分数放在最后一行,但不是最大最小的。处理过程如下记录:
SELECT '工单' AS BILTYPENAME,
CASE WHEN BUREAU_ID IS NULL THEN '小计'
ELSE BUREAU_ID
END AS BUREAU_ID,
CASE WHEN BILLSERVER_ENNAME IS NULL AND BUREAU_ID IS NOT NULL
THEN '小计'
ELSE BILLSERVER_ENNAME
END AS BILLSERVER_ENNAME,
SUM(TOTAL) AS TOTAL,
SUM(SUCC_NUM) AS SUCCNUM,
SUM(FAIL_NUM) AS FAILNUM,
DECODE(SUM(SUCC_NUM) / SUM(TOTAL),NULL,'0',1,'100',
TO_CHAR(ROUND(SUM(SUCC_NUM) / SUM(TOTAL), 4) * 100
,'fm90.00')) || '%'
AS SUCCESSRATE,
DECODE(BILLSERVER_ENNAME, NULL, -1, 1)
*ROUND(SUM(SUCC_NUM) / SUM(TOTAL), 4) AS PAIXU
FROM APP_ORIGINBILL_STAT_DAY ORIGINBILLOBJ
WHERE 1 = 1
GROUP BY ROLLUP(BUREAU_ID, BILLSERVER_ENNAME)
ORDER BY BUREAU_ID NULLS LAST, PAIXU DESC
这里百分数用 DECODE(SUM(SUCC_NUM) / SUM(TOTAL),NULL,'0',1,'100',
TO_CHAR(ROUND(SUM(SUCC_NUM) / SUM(TOTAL), 4) * 100 ,'fm90.00')) || '%' 来表示,这样最后排序时,有可能小计位于中间位置,通过 PAIXU 字段 DECODE(BILLSERVER_ENNAME, NULL, -1, 1)
*ROUND(SUM(SUCC_NUM) / SUM(TOTAL), 4) 重新进行降序排序,可以真正实现如图的排序。
应该有更好的方法,希望能提供!