我正在嘗試運行此查詢，但是當它到達計數函式時，我不斷收到錯誤訊息，說“不允許使用組函式”。
select student.student_id,student.first_name,student.last_name, enrollment.enroll_date
from student
left outer join Enrollment
on student.student_id = enrollment.student_id
left join Section on section.section_id = enrollment.section_id
left join course on section.course_no = course.course_no
where count(course.course_no) = 0
and enrollment.enroll_date = TO_DATE('2/15/2007', 'mm/dd/yyyy');
uj5u.com熱心網友回復：
你能試試下面的查詢嗎？
select student.student_id,student.first_name,student.last_name, enrollment.enroll_date
from student
left outer join Enrollment
on student.student_id = enrollment.student_id
left join Section on section.section_id = enrollment.section_id
left join course on section.course_no = course.course_no
where course.course_no is null
and enrollment.enroll_date = TO_DATE('2/15/2007', 'mm/dd/yyyy');
uj5u.com熱心網友回復：
COUNTWHERE無論如何不能用在子句中；您應該將其移動到HAVING，但是您還需要一個GROUP BY. 此外，習慣使用表別名并嘗試對其進行格式化；使代碼更易于閱讀。像這樣的東西：
select s.student_id, s.first_name, s.last_name, e.enroll_date
from student s left join enrollment e on s.student_id = e.student_id
left join section t on t.section_id = e.section_id
left join course c on t.course_no = c.course_no
where e.enroll_date = TO_DATE('2/15/2007', 'mm/dd/yyyy')
group by s.student_id, s.first_name, s.last_name, e.enroll_date
having count(c.course_no) = 0;
為了說明這一點（因為我沒有您的表格），請查看以下基于 ScottEMP表格的示例：
這是你試過的：
SQL> select job
2 from emp
3 where count(comm) = 0;
where count(comm) = 0
*
ERROR at line 3:
ORA-00934: group function is not allowed here
正如我所說，count不能在where子句中使用。所以改寫為：
SQL> select job
2 from emp
3 group by job
4 having count(comm) = 0;
JOB
---------
CLERK
PRESIDENT
MANAGER
ANALYST
SQL>
現在它起作用了。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/368560.html