mysql - 从表中选择列的所有内容

Question

在我的 mysql 数据库中，我有以下表格:

FACULTY (fid int, fname varchar(25), deptid int, primary key(fid))  
CLASS (name varchar(4),meets_at varchar(9),room varchar(4), fid int,primary key (name), foreign key (fid) references faculty (fid))  

我想选择进入所有房间的教师姓名。 我尝试使用以下方法:

SELECT DISTINCT F.FNAME   
FROM FACULTY F  
WHERE NOT EXISTS (( SELECT *  
FROM CLASS C  
EXCEPT  
(SELECT C1.ROOM  
FROM CLASS C1  
   WHERE C1.FID=F.FID))); 

并出现以下错误:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXCEPT

还尝试过:

SELECT DISTINCT F.FNAME   
FROM FACULTY F   
LEFT JOIN CLASS C ON C.FID = F.FID  
WHERE C.FID IS NULL  

即使在我的数据库中有一位教员去了所有房间，也得到了“空集”。

Answer 1

当您使用 except时，两个表必须兼容，请尝试以下操作:

SELECT DISTINCT F.FNAME   
FROM FACULTY F  
WHERE NOT EXISTS (
( SELECT ROOM  
FROM CLASS C)
EXCEPT  
(SELECT C1.ROOM  
FROM CLASS C1  
   WHERE C1.FID=F.FID)
); 

编辑

这个问题被标记为 sql server，所以我给出了答案，记住这一点，对于 mysql 使用这个:

SELECT FID, COUNT(*) FROM
(
    (SELECT DISTINCT f.fname, f.fid, c1.room 
    FROM faculty f
    JOIN class c1 
    ON f.fid = c1.fid) tb1 
JOIN
    (SELECT DISTINCT room AS room2 FROM class) tb2
ON tb1.room = tb2.room2
)
GROUP BY FID
HAVING COUNT(*) IN (SELECT COUNT(DISTINCT Room) FROM Class);

fiddle :http://sqlfiddle.com/#!8/cff12/4