如何通过查询获取累计月度数据

Question

当我运行这个查询时，由于某种原因，它应该在DAY列(210301,201302，SUM).But下显示3行结果，它只在DAY列下显示SUM行。想法是'201301‘行在1月份累积了31天的数据。有没有人能告诉我他们为什么不出现？

    SELECT X.*
 FROM
      ( 
        SELECT 
                    (CASE
                             WHEN DAY.DAY = 'SUM' THEN DAY.DAY
                             ELSE 
                             TO_CHAR (TO_DATE (DAY.DAY, 'YYYY-MM'), 'YYYYMM') 
                       END
                    ) AS DAY,
                    MNOT.SUM_STD_CNT AS MNOT_CNT,
                    RSC.MOVIE_500K AS MOVIE_500K_CNT,
                    RSC.MOVIE_1M AS MOVIE_1M_CNT,
                    RSC.MOVIE AS MOVIE_CNT,
                    RSC.EXAM AS EXAM_CNT,
                    RSC.WEB AS WEB_CNT,
                    RSC.IMG AS IMG_CNT,
                    RSC.INTERRAC AS INTERRACTIVE_CNT,
                    RSC.DOC AS DOC_CNT,
                    MNOT.SUM_STD_CNT + RSC.MOVIE_500K + RSC.MOVIE_1M + RSC.MOVIE + RSC.EXAM + RSC.WEB + RSC.IMG + RSC.INTERRAC + RSC.DOC TOT_CNT
          FROM 
                    /* DATES */

                   (SELECT TO_CHAR(ADD_MONTHS(TO_DATE('201301','YYYYMM'),LEVEL-1), 'YYYYMM') AS DAY
                      FROM DUAL
                      CONNECT BY ADD_MONTHS(TO_DATE('201301','YYYYMM'),LEVEL-1 ) <= TO_DATE('201302', 'YYYYMM')
                      UNION ALL
                      SELECT 'SUM' FROM DUAL
                    ) DAY LEFT OUTER JOIN

                   /* RESOURCE */
                    (
                     SELECT 
                                 NVL(DT_G.COMM_DT, 'SUM') COMM_DT
                                ,NVL(SUM(DT_G.MOVIE_500K), 0) AS MOVIE_500K
                                ,NVL(SUM(DT_G.MOVIE_1M), 0) AS MOVIE_1M
                                ,NVL(SUM(DT_G.MOVIE), 0) AS MOVIE
                                ,NVL(SUM(DT_G.EXAM), 0) AS EXAM
                                ,NVL(SUM(DT_G.DOC), 0) AS DOC 
                                ,NVL(SUM(DT_G.IMG), 0) AS IMG
                                ,NVL(SUM(DT_G.WEB), 0) AS WEB
                                ,NVL(SUM(DT_G.INTERRAC), 0) AS INTERRAC
                       FROM 
                                (
                                 SELECT
                                              COMM_DT
                                            , CASE SUMT.RSC_TP_DSCD WHEN 'RTP10' THEN SUMT.SUM_500K END MOVIE_500K
                                            , CASE SUMT.RSC_TP_DSCD WHEN 'RTP10' THEN SUMT.SUM_1M END AS MOVIE_1M
                                            , CASE SUMT.RSC_TP_DSCD WHEN 'RTP10' THEN SUMT.SUM_500K+SUMT.SUM_1M END AS MOVIE
                                            , CASE SUMT.RSC_TP_DSCD WHEN 'RTP11' THEN SUMT.SUM_STD_CNT END AS EXAM
                                            , CASE SUMT.RSC_TP_DSCD WHEN 'RTP12' THEN SUMT.SUM_STD_CNT END AS DOC
                                            , CASE SUMT.RSC_TP_DSCD WHEN 'RTP13' THEN SUMT.SUM_STD_CNT END AS IMG
                                            , CASE SUMT.RSC_TP_DSCD WHEN 'RTP14' THEN SUMT.SUM_STD_CNT END AS WEB
                                            , CASE SUMT.RSC_TP_DSCD WHEN 'RTP01' THEN SUMT.SUM_STD_CNT END AS INTERRAC
                                  FROM (
                                              SELECT RSC_TP_DSCD, SUM(STDY_CNT) AS SUM_STD_CNT, SUM(MOVIE_STDY_CNT_N1M) AS SUM_1M, SUM(MOVIE_STDY_CNT_N500K) AS SUM_500K, COMM_DT
                                              FROM (
                                                        SELECT RSC_SNO, STDY_CNT, MOVIE_STDY_CNT_N1M, MOVIE_STDY_CNT_N500K, COMM_DT
                                                          FROM LRMS.V_EBSM_PKG_RSC_COMM_CNT
                                                        WHERE PKG_SNO = 0  AND RSC_SNO != 0
                                                        AND COMM_DT BETWEEN TO_CHAR(TO_DATE('201301', 'YYYY-MM'), 'YYYYMM') AND TO_CHAR(TO_DATE('201311', 'YYYY-MM'), 'YYYYMMDD')
                                                        ) CNT
                                                   LEFT OUTER JOIN LRMS.V_LRRM_RSC RSC ON CNT.RSC_SNO = RSC.RSC_SNO
                                              GROUP BY RSC_TP_DSCD, COMM_DT
                                            ) SUMT
                                ) DT_G
                         GROUP BY ROLLUP(DT_G.COMM_DT)
                    ) RSC ON DAY.DAY = RSC.COMM_DT
                    /* MNOTE */
                    LEFT OUTER JOIN
                    (
                      SELECT NVL(SUM(STDY_CNT), 0) AS SUM_STD_CNT, NVL(COMM_DT, 'SUM') COMM_DT
                        FROM LRMS.V_EBSM_PKG_RSC_COMM_CNT
                      WHERE PKG_SNO != 0  AND RSC_SNO = 0
                                AND COMM_DT BETWEEN TO_CHAR(TO_DATE('201301', 'YYYY-MM'), 'YYYYMM') AND TO_CHAR(TO_DATE('201306', 'YYYY-MM'), 'YYYYMM')
                      GROUP BY ROLLUP(COMM_DT)
                    ) MNOT  ON DAY.DAY = MNOT.COMM_DT
        ) X

 WHERE 1=1
 and X.TOT_CNT IS NOT NULL 

Answer 1

这可能是由于sum字段TOT_CNT中的空值造成的

更改线路

MNOT.SUM_STD_CNT + RSC.MOVIE_500K + RSC.MOVIE_1M + RSC.MOVIE + RSC.EXAM + RSC.WEB + RSC.IMG + RSC.INTERRAC + RSC.DOC TOT_CNT

至

nvl(MNOT.SUM_STD_CNT,0) + nvl(RSC.MOVIE_500K,0) + nvl(RSC.MOVIE_1M,0) + nvl(RSC.MOVIE,0) + nvl(RSC.EXAM,0) + nvl(RSC.WEB,0) + nvl(RSC.IMG,0) + nvl(RSC.INTERRAC,0) + nvl(RSC.DOC,0) AS TOT_CNT

并尝试