Oracle基本汇总报表

SELECT

DECODE(
DECODE(GROUPING(wxts.ITID) + GROUPING(wxts.PRO_ENAME),2,1,0) +GROUPING(to_char(wxt.PAYTIME,'yyyy-mm-dd')
) ,1,'日期小计',2,'合计',to_char(wxt.PAYTIME,'yyyy-mm-dd')) PAYTIME,
或
DECODE(
GROUPING(wxts.ITID) + GROUPING(wxts.PRO_ENAME)+GROUPING(to_char(wxt.PAYTIME,'yyyy-mm-dd')
) ,2,'日期小计',3,'合计',to_char(wxt.PAYTIME,'yyyy-mm-dd')) PAYTIME,

wxts.ITID,
wxts.PRO_ENAME,
SUM(CASE WHEN sp.manaid = '00002338' THEN NVL(wxts.slnumber,0) ELSE 0 END) AS 微信公众号店铺数量,
SUM(CASE WHEN sp.manaid = '00002338' THEN NVL(wxts.amountsl,0) ELSE 0 END) AS 微信公众号店铺金额

FROM sy_management sp
LEFT JOIN wh_warehouse wh ON sp.manaid = wh.whtel
LEFT JOIN POS_MACHINE p ON wh.whid = p.whid
FULL JOIN t_biz_wxorder wxt  ON p.machineid = wxt.machineid AND wxt.GATHERSIGN = '1' AND wxt.isuse = '0' AND wxt.PAYTIME IS NOT NULL
LEFT JOIN t_biz_wxorderdl wxts ON wxts.CPID = wxt.CPID AND wxts.GFBILLID = wxt.GFBILLID
WHERE NVL(sp.isdel,'0') = '0' AND sp.isuse = '1' AND sp.mananame IS NOT NULL AND sp.cpid = '00001081'

GROUP   BY   ROLLUP(to_char(wxt.PAYTIME,'yyyy-mm-dd'),(wxts.ITID,wxts.PRO_ENAME))--这个需要注意
ORDER   BY   to_char(wxt.PAYTIME,'yyyy-mm-dd'),wxts.PRO_ENAME