显示在同一学期内教授至少 2 门课程的所有教授的不同身份证

Question

我在做这个SQL作业问题时遇到了一点麻烦，并且尝试了多次不同的尝试，但总是得到相同的错误答案。我不确定我错过了什么，如果有人能帮助我，那就太好了！:)

问题：显示在同一学期至少教授过 2 门课程的所有教授的不同身份证

以下是提供给我们的表格：

DROP TABLE IF EXISTS PROFESSOR;  
CREATE TABLE PROFESSOR (  
    Id   VARCHAR (9),   
    Name    VARCHAR (32),  
    DepartmentId  VARCHAR (3),   
    PRIMARY KEY (Id),  
    FOREIGN KEY (DepartmentId) REFERENCES DEPARTMENT (Id)  
)ENGINE=INNODB;
INSERT INTO PROFESSOR VALUES  
('P01', 'Rao',      'CSC'),  
('P02', 'Mitra',    'CSC'),  
('P03', 'Smith',    'MTH'),  
('P04', 'Miller',   'MTH'),  
('P05', 'Abwender', 'PSH'),  
('P06', 'Speed',    'PSH');  
DROP TABLE IF EXISTS TEACHING_ASSIGNMENT;  
CREATE TABLE TEACHING_ASSIGNMENT (  
    ProfessorId VARCHAR (9), -- Department Code may be different   
    CourseCode  VARCHAR (6), -- This is like CIS422  
    Semester    VARCHAR (16),-- This is like Fall 2016  
    Section     VARCHAR (3), -- This is like 01  
    PRIMARY KEY (ProfessorId, CourseCode, Semester, Section),  
    FOREIGN KEY (ProfessorId) REFERENCES PROFESSOR (Id),  
    FOREIGN KEY (CourseCode)  REFERENCES COURSE (CourseCode)  
)ENGINE=INNODB;  

INSERT INTO TEACHING_ASSIGNMENT VALUES  
('P01', 'CSC203', 'Fall 2015', '01'),  
('P02', 'CIS202', 'Fall 2015', '01'),  
('P03', 'MTH201', 'Fall 2015', '01'),  
('P04', 'MTH281', 'Fall 2015', '01'),  
('P05', 'PSH110', 'Fall 2015', '01'),  
('P06', 'PSH201', 'Fall 2015', '01'),  
('P01', 'CIS202', 'Fall 2015', '02'),  
('P03', 'CSC203', 'Spring 2016', '01'),  
('P02', 'CIS202', 'Spring 2016', '01'),  
('P03', 'MTH201', 'Spring 2016', '01'),  
('P04', 'MTH281', 'Spring 2016', '01'),  
('P05', 'PSH110', 'Spring 2016', '01'),  
('P06', 'PSH201', 'Spring 2016', '01'),  
('P01', 'CIS202', 'Spring 2016', '02');  

我的 SQL：

SELECT DISTINCT Id
FROM PROFESSOR
WHERE Id IN
    (SELECT ProfessorId
     FROM TEACHING_ASSIGNMENT
     GROUP BY Semester
     HAVING COUNT() >= 2);

我得到的答案是：P01

但正确的答案应该是 P01 和 P03。

Answer 1

不需要子查询：

SELECT ProfessorId
FROM TEACHING_ASSIGNMENT
GROUP BY Semester, ProfessorId
HAVING COUNT(*) >= 2;

Answer 2

GROUP BY应该包括ProfessorId因为您正在计算教授在一个学期内教授的课程数量。试试这个：

SELECT DISTINCT Id
FROM PROFESSOR
WHERE Id IN
    (SELECT ProfessorId
     FROM TEACHING_ASSIGNMENT
     GROUP BY Semester, ProfessorId
     HAVING COUNT(*) >= 2);

Answer 3

下面是查询，我在创建新表后检查了它。

select Courses.ProfessorId, count(*) from Cou_Pro Courses 
group by Courses.ProfessorId, Courses.Semester having
count(Courses.ProfessorId) > 1