oracle小计

来源：互联网发布：淘客助手插件 mac 编辑：程序博客网时间：2024/06/10 20:10

SELECT
      -- decode(BM_XH,'', '小计', BM_XH) BM_XH,
       --decode(BM_MC, '', '小计', BM_XH) BM_XH,
       BM_XH,
      -- BM_MC AS 部门名称,
        decode(BM_MC, '', '小计', BM_MC) 部门名称,
       TO_CHAR(GD_RQ, 'YYYY-MM-DD') AS 日期,
       NVL(SUM(FX * (CASE
                 WHEN GD_YHLB = 1 THEN
                  1
               END)),
           0) AS 局收动力发,
       NVL(SUM(JF * (CASE
                 WHEN GD_YHLB = 1 THEN
                  1
               END)),
           0) AS 局收动力交,
       NVL(SUM(WYJ * (CASE
                 WHEN GD_YHLB = 1 THEN
                  1
               END)),
           0) AS 局收动力违,
       NVL(SUM(FX * (CASE
                 WHEN GD_YHLB = 3 THEN
                  1
               END)),
           0) AS 综合动力发,
       NVL(SUM(JF * (CASE
                 WHEN GD_YHLB = 3 THEN
                  1
               END)),
           0) AS 综合动力交,
       NVL(SUM(WYJ * (CASE
                 WHEN GD_YHLB = 3 THEN
                  1
               END)),
           0) AS 综合动力违,
       NVL(SUM(FX * (CASE
                 WHEN GD_YHLB = 2 THEN
                  1
               END)),
           0) AS 居民照明发,
       NVL(SUM(JF * (CASE
                 WHEN GD_YHLB = 2 THEN
                  1
               END)),
           0) AS 居民照明交,
       NVL(SUM(WYJ * (CASE
                 WHEN GD_YHLB = 2 THEN
                  1
               END)),
           0) AS 居民照明违,
       BM_BM
FROM YD_RJB
LEFT JOIN YG_BM ON GD_BM = BM_BM
WHERE TO_CHAR(GD_RQ, 'YYYY-MM-DD') >= '2008-07-20'
   AND TO_CHAR(GD_RQ, 'YYYY-MM-DD') <= '2008-09-20'
--GROUP BY rollup(BM_XH, BM_BM, BM_MC, TO_CHAR(GD_RQ, 'YYYY-MM-DD'))
GROUP BY grouping sets((BM_XH), (BM_XH, BM_BM, BM_MC, TO_CHAR(GD_RQ, 'YYYY-MM-DD')))