我正在尝试让老师FIRSTNAME
,LASTNAME
和他获得的课程数量。甲骨文数据库。 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;
我的错误在哪里?
听起来你需要一个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
WHERE 子句
后面缺少 GROUP BY 子句
按名字、姓氏分组 --除聚合函数(如 COUNT(中的列之外的所有选定列
也许你可以试试
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