mysql 查询未返回预期值

Question

嗨，我正在为以下数据库结构编写一个 mysql 查询。

Professor (EMP ID,Name,Status,Salary,Age)
Course(Course ID,Course Name,Points)
Works(Course ID,EMP ID,Class ID)
Assumptions:
Each course has only one instructor in each semester
Status can take values from “Full”, “Associate”, and “Assistant”

我需要执行以下操作。

返回曾在一个类(class)中至少教授过两门类(class)的正教授姓名

SELECT p.name
FROM professor p, works w
WHERE p.empid = w.empid
AND p.status = ‘full’
AND w.classid IN (SELECT classid
FROM works
WHERE count(courseid)>1)

返回在 10 类教授最多类(class)的教授姓名

SELECT p.names
FROM professor p, works w
WHERE p.empid =w.empid
AND w.classid IN (SELECT classid
FROM works
WHERE classid = 10
AND coursed = max(coursed))

但是这些查询返回错误的结果。我是 mysql 新手，请帮忙。

Answer 1

试试这样

SELECT 
    p.*,
    COUNT(c.Course_ID) AS TCount
FROM Professor AS P
LEFT JOIN Works AS w ON w.EMP_ID = p.EMP_ID
LEFT JOIN Course AS c ON w.Course_ID = c.Course_ID
GROUP BY p.EMP_ID , c.Course_ID
HAVING TCount > 1