时间戳

首页 > 解决方案 > 如何修复 oracle apex 中的“最大计数”

oracle - 如何修复 oracle apex 中的“最大计数”

问题描述

最大计数未在 Oracle Apex 中提供正确的值

SELECT  'TOTAL' label,
MAX(COUNT(REPORT_DATE )) value FROM DATA
Where STATUS = 'Open' 
  and MANAGED_BY = 'Internal' 
  and MANAGER = 'Vinod' 
  and SECOND_LEVEL_MANAGER = 'Kumar'
GROUP BY REPORT_DATE

标签： oracleoracle-apexoracle-apex-5

解决方案

样本数据会有所帮助；同时，如果我对您的理解正确，我会说您需要一个where子句，而不是group by. 原因如下：这是本月的日历：

SQL> with calendar as
  2    (select trunc(sysdate, 'mm') + level - 1 datum
  3     from dual
  4     connect by level <= 31
  5    )
  6  select datum,
  7         to_char(datum, 'day', 'nls_date_language=english') day,
  8         to_char(datum, 'iw') week
  9  from calendar
 10  --where to_char(datum, 'iw') = to_char(sysdate, 'iw')
 11  order by datum;

DATUM      DAY                                  WE
---------- ------------------------------------ --
01.07.2019 monday                               27
02.07.2019 tuesday                              27
03.07.2019 wednesday                            27
04.07.2019 thursday                             27
05.07.2019 friday                               27
06.07.2019 saturday                             27
07.07.2019 sunday                               27
08.07.2019 monday                               28
09.07.2019 tuesday                              28
10.07.2019 wednesday                            28
11.07.2019 thursday                             28
12.07.2019 friday                               28
13.07.2019 saturday                             28
14.07.2019 sunday                               28
15.07.2019 monday                               29
16.07.2019 tuesday                              29
17.07.2019 wednesday                            29
18.07.2019 thursday                             29
19.07.2019 friday                               29
20.07.2019 saturday                             29
21.07.2019 sunday                               29
22.07.2019 monday                               30
23.07.2019 tuesday                              30
24.07.2019 wednesday                            30       --> today
25.07.2019 thursday                             30
26.07.2019 friday                               30
27.07.2019 saturday                             30
28.07.2019 sunday                               30
29.07.2019 monday                               31
30.07.2019 tuesday                              31
31.07.2019 wednesday                            31

31 rows selected.

SQL>

今天的星期是30, 所以 - 如果你取消注释where子句（第 10 行），你只会得到本周：

SQL> l10
 10* --where to_char(datum, 'iw') = to_char(sysdate, 'iw')
SQL> c/--//
 10* where to_char(datum, 'iw') = to_char(sysdate, 'iw')
SQL> /

DATUM      DAY                                  WE
---------- ------------------------------------ --
22.07.2019 monday                               30
23.07.2019 tuesday                              30
24.07.2019 wednesday                            30
25.07.2019 thursday                             30
26.07.2019 friday                               30
27.07.2019 saturday                             30
28.07.2019 sunday                               30

7 rows selected.

SQL>

这就是你应该做的（见最后一行）；此外，您将不再需要两个嵌套聚合函数 -count应该足够了。此外，如果你在没有子句ORA-00978: nested group function without GROUP BY的情况下保留嵌套聚合，你会得到；group by是的，你可以保留它，但为什么呢？没必要。

select 'TOTAL' label, 
       count(report_date) value 
from data 
where status = 'Open' 
  and managed_by = 'Internal' 
  and manager = 'Vinod' 
  and second_level_manager = 'Kumar' 
  --
  and to_char(report_date, 'iw') = to_char(sysdate, 'iw')      --> this

推荐阅读