SQL查找教师课程查询

Question

我正试着让FIRSTNAME老师，LASTNAME老师，还有他的课程数。Oracle数据库CLASS表有列：CLASSID、TEACHERID、CLASSNAME当前代码：

SELECT DISTINCT FIRSTNAME, LASTNAME, COUNT(TEACHERID)
FROM PERSON 
INNER JOIN TEACHER T ON PERSON.PERSONID = T.TEACHERID 
INNER JOIN CLASS C ON T.TEACHERID = C.TEACHERID
WHERE T.TEACHERID = C.TEACHERID;

我的错误在哪里？

Answer 1

听起来你需要一个GroupBy：

SELECT FIRSTNAME, LASTNAME, COUNT(T.TEACHERID)
FROM PERSON 
INNER JOIN TEACHER T ON PERSON.PERSONID = T.TEACHERID 
INNER JOIN CLASS C ON T.TEACHERID = C.TEACHERID
GROUP BY LASTNAME,FIRSTNAME

顺便说一下，我最初删除了这个答案，因为这不是一个好的分组方式。相反，我更喜欢按TEACHERID分组，然后加入以获得名称，而不是按名称分组。

我认为这是一个更好的方法：

SELECT FIRSTNAME, LASTNAME, C.NUMCLASSES
FROM PERSON 
INNER JOIN TEACHER T ON PERSON.PERSONID = T.TEACHERID 
INNER JOIN (SELECT TEACHERID, COUNT(CLASSID) AS NUMCLASSES FROM CLASS GROUP BY TEACHERID) C
ON C.TEACHERID=T.TEACHERID

Answer 2

在WHERE子句之后缺少GROUP BY子句。

按FIRSTNAME、LASTNAME、LASTNAME-除聚集函数(如COUNT )中的列之外的所有选定列

Answer 3

也许你可以试试

SELECT FIRSTNAME, LASTNAME, 
(select COUNT(C.TEACHERID) form CLASS C ON T.TEACHERID = C.TEACHERID)
as COURSECOUNT
FROM PERSON 
INNER JOIN TEACHER T ON PERSON.PERSONID = T.TEACHERID