我被这个卡住了,我希望有人能帮助我:
SELECT max(SELECT count(TO_CHAR(hire_date, 'DAY'))
FROM employees
GROUP BY TO_CHAR(hire_date, 'DAY'))
FROM employees;
*
ERROR at line 1:
ORA-00936: missing expression子查询的输出如下所示:
TO_CHAR(HIRE_DATE,'DAY') COUNT(TO_CHAR(HIRE_DATE,'DAY'))
---------------------------------------- -------------------------------
THURSDAY 3
SATURDAY 3
WEDNESDAY 4
MONDAY 1
SUNDAY 3
TUESDAY 6我只想选择星期二。
发布于 2017-06-11 04:41:44
可以通过向内部查询的SELECT子句添加分析函数RANK()来完成此操作。然后在外部查询中选择排名为1的行。这将生成所有“并列第一”的天数(要么是单个获胜者,要么是并列第一名)。
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions123.htm (寻找分析版本!)
这个to_char(....)看起来很丑陋,但是由于它是确定性的,甲骨文只计算一次(即使它在四个地方使用)。
select dy, ct
from (
select to_char(hire_date, 'DAY') as dy,
count(to_char(hire_date, 'DAY')) as ct,
rank() over (order by count(to_char(hire_date, 'DAY')) desc) as rk
from hr.employees
group by to_char(hire_date, 'DAY')
)
where rk = 1
;发布于 2017-06-11 04:34:05
问题中的子查询将不会产生所描述的输出。请尝试使用具有此功能的子查询。
select max( total ) from (
SELECT TO_CHAR(HIRE_DATE,'DAY') day
, count(TO_CHAR(hire_date, 'DAY')) total
FROM employees
GROUP BY TO_CHAR(hire_date, 'DAY')
) daysTotals在甲骨文上未经测试。
发布于 2017-06-11 04:41:22
您可以使用HAVING-clause中的子查询来解决此问题:
select TO_CHAR(hire_date, 'DAY') as day, count(TO_CHAR(hire_date, 'DAY')) as cnt
FROM employees
GROUP BY TO_CHAR(hire_date, 'DAY'))
HAVING count(TO_CHAR(hire_date, 'DAY')) >= ALL (
SELECT count(TO_CHAR(hire_date, 'DAY'))
FROM employees
GROUP BY TO_CHAR(hire_date, 'DAY')https://stackoverflow.com/questions/44477682
复制相似问题