Oracle——分组汇总统计函数grouping()
在Oracle数据库中,进行分组报表查询的时候,查询的SQL中可以使用grouping()函数。
grouping(columnA)函数的意思:当前行如果是由rollup汇总产生的,那么columnA这个字段值为1否则为0;
元数据:
通过grouping查询后的数据:
查询的SQL:
SELECT
DECODE(grouping (T0.pay_kind)+grouping (T0.oper_code),1,T0.oper_code,2,'总计',T0.oper_code) AS "placeType",
DECODE(grouping (T0.pay_kind), 1, '小计', T0.pay_kind ) AS "payType",
SUM( T0.reg_fee ) AS REGTOTAL,
SUM( T0.chck_fee ) AS CHCKTOTAL,
SUM( T0.diag_fee ) AS DIAGTOTAL,
SUM( T0.oth_fee ) AS OTHTOTAL,
COUNT( T0.reg_fee ) AS COUNT
FROM
fin_opr_register @to_dr t0
WHERE
T0.OPER_date BETWEEN TO_DATE( '2018-01-01', 'yyyy-mm-dd' )
AND TO_DATE( '2019-08-30', 'yyyy-mm-dd' )
AND T0.oper_code IN ( 'XYZZJQ', 'NYZZJQ', 'ZXZZJQ', 'BYZZJQ', 'DYZZJQ' )
AND T0.pay_kind IN ( 'AC', 'CD', 'CA' )
GROUP BY rollup ( T0.oper_code, T0.pay_kind )