Oracle基本汇总报表

最新推荐文章于 2022-01-19 10:38:29 发布

weixin_30402085

最新推荐文章于 2022-01-19 10:38:29 发布

阅读量446

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/zuochencun/p/9414895.html

版权

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

转载于:https://www.cnblogs.com/zuochencun/p/9414895.html