在Oracle中使用IW和MM [英] Using IW and MM in Oracle

问题描述

我将IW用于每周结果，MM用于每月结果。但我总是得到一个错误：

  ORA-00979：不是GROUP BY表达式
 00979. 00000  - not一个GROUP BY表达式
  

我的查询就是这些

（每周）

  SELECT'Data'
 || '，'|| TO_CHAR（d.dtime_day，'MM / dd / yyyy'）
 || '，'|| NVL（o.cnt_opened，0） - 作为cnt_opened 
 || '，'|| NVL（c.cnt_closed，0）--as cnt_closed 
 FROM owner_dwh.dc_date d 
 LEFT JOIN（
 SELECT 
 TRUNC（t.create_time，'IW'）AS report_date， 
 count（*）AS cnt_opened 
 FROM app_account.otrs_ticket t 
 WHERE t.create_time BETWEEN SYSDATE  -  30和SYSDATE 
 GROUP BY TRUNC（t.create_time）
） o ON d.dtime_day = o.report_date 
 LEFT JOIN（
 SELECT 
 TRUNC（t.close_time，'IW'）AS report_date，
 count（*）AS cnt_closed 
 FROM app_account.otrs_ticket t 
 WHERE t.close_time BETWEEN SYSDATE  -  30 AND SYSDATE 
 GROUP BY TRUNC（t.close_time）
）c ON d.dtime_day = c.report_date 
 WHERE d.dtime_day BETWEEN SYSDATE  -  30和SYSDATE 
 ORDER BY d.dtime_day; 
  

（每月）

  SELECT'Graph，ColumnChart，Open vs. Close Issues'FROM DUAL; 
 
从双重选择'数据，日期，已打开，已关闭'; 
 
 SELECT'Data'
 || '，'|| TO_CHAR（d.dtime_day，'MM / dd / yyyy'）
 || '，'|| NVL（o.cnt_opened，0） - 作为cnt_opened 
 || '，'|| NVL（c.cnt_closed，0）--as cnt_closed 
 FROM owner_dwh.dc_date d 
 LEFT JOIN（SELECT 
 TRUNC（t.create_time，'MM'）AS report_date，
 count（*）AS cnt_opened 
 FROM app_account.otrs_ticket t 
 WHERE t.create_time BETWEEN SYSDATE  -  365 AND SYSDATE 
 GROUP BY TRUNC（t.create_time）; 
）o ON d .DTIME_DAY = o.REPORT_DATE 
 LEFT JOIN（SELECT TRUNC（t.CLOSE_TIME，'MM'）AS report_date，$ b $ count（*）AS cnt_closed 
 FROM APP_ACCOUNT.OTRS_TICKET t 
 （t.CLOSE_TIME）; 
）c ON D.DTIME_DAY = C.REPORT_DATE 
 WHERE d.DTIME_DAY SYWATE -365 AND SYSDATE 
 ORDER BY D.DTIME_DAY; 
  

解决方案

您必须按照您在SELECT子句。

所以如果你：

  SELECT TRUNC（ t.CREATE_TIME，'MM'）AS report_date，count（*）AS cnt_opened 
  

... then你必须... ...
$ b $ pre $ GROUP BY TRUNC（t.CREATE_TIME，'MM'）


I'm using IW for weekly result and MM for Monthly result. But I always get an error:

ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"

My queries are these

(Weekly)

SELECT 'Data'
       || ',' || TO_CHAR(d.dtime_day, 'MM/dd/yyyy')
       || ',' || NVL(o.cnt_opened, 0) --as cnt_opened
       || ',' || NVL(c.cnt_closed, 0) --as cnt_closed
FROM owner_dwh.dc_date d
  LEFT JOIN (
              SELECT
                TRUNC(t.create_time, 'IW') AS report_date,
                count(*)                   AS cnt_opened
              FROM app_account.otrs_ticket t
              WHERE t.create_time BETWEEN SYSDATE - 30 AND SYSDATE
              GROUP BY TRUNC(t.create_time)
            ) o ON d.dtime_day = o.report_date
  LEFT JOIN (
              SELECT
                TRUNC(t.close_time, 'IW') AS report_date,
                count(*)                  AS cnt_closed
              FROM app_account.otrs_ticket t
              WHERE t.close_time BETWEEN SYSDATE - 30 AND SYSDATE
              GROUP BY TRUNC(t.close_time)
            ) c ON d.dtime_day = c.report_date
WHERE d.dtime_day BETWEEN SYSDATE - 30 AND SYSDATE
ORDER BY d.dtime_day;

(Monthly)

SELECT 'Graph,ColumnChart,Open vs. Close Issues' FROM DUAL;

SELECT 'Data,Date,Opened,Closed' from dual;

SELECT 'Data'
       || ',' || TO_CHAR(d.dtime_day, 'MM/dd/yyyy')
       || ',' || NVL(o.cnt_opened, 0) --as cnt_opened
       || ',' || NVL(c.cnt_closed, 0) --as cnt_closed
FROM owner_dwh.dc_date d
  LEFT JOIN ( SELECT
                TRUNC(t.create_time, 'MM') AS report_date,
                count(*)                   AS cnt_opened
                FROM app_account.otrs_ticket t
               WHERE t.create_time BETWEEN SYSDATE - 365 AND SYSDATE
               GROUP BY TRUNC(t.create_time);
            ) o ON d.DTIME_DAY=o.REPORT_DATE
  LEFT JOIN ( SELECT TRUNC(t.CLOSE_TIME, 'MM') AS report_date,
                     count(*)                  AS cnt_closed
                FROM APP_ACCOUNT.OTRS_TICKET t
               WHERE t.CLOSE_TIME BETWEEN SYSDATE -365 AND SYSDATE
               GROUP BY TRUNC(t.CLOSE_TIME);
          ) c ON D.DTIME_DAY= C.REPORT_DATE
WHERE d.DTIME_DAY BETWEEN SYSDATE -365 AND SYSDATE
ORDER BY D.DTIME_DAY;

解决方案

You have to group by the same expression you use in the SELECT clause.

So if you:

SELECT TRUNC(t.CREATE_TIME, 'MM') AS report_date,count(*) AS cnt_opened

... then you have to ...

GROUP BY TRUNC(t.CREATE_TIME,'MM')

这篇关于在Oracle中使用IW和MM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！