mysql查询不返回预期值
问题描述:
嗨,我正在为下面的数据库结构写一个mysql查询。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”
我需要执行以下操作。
返回谁教过至少两个疗程在一类
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
类的教授(或多个)归来的姓名(或名称)全职教授的名字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请帮忙。
答
尝试像这样
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
+0
这是哪个问题我有2 – user1844638 2013-03-08 11:45:02
+0
@ user1844638你不能至少测试一下,看看你自己吗? – 2013-03-08 11:46:07
答
的第一个备选答案:
SELECT DISTINCT p.empid, p.name
FROM professor AS p
JOIN works AS w ON
p.empid = w.empid
JOIN works w2 ON
w.empid=w2.empid AND w.classid=w2.classid AND w.courseid<>w2.courseid
你应该阅读有关明确连接,GROUP BY和聚集。 – 2013-03-08 11:48:23