mysql - mysql查询输出投影仪和白板

我有这个小任务：
查找有投影仪但没有白板的房间。

CREATE TABLE Equipment
(
room VARCHAR(15),
type VARCHAR(20)
);

INSERT INTO Equipment VALUES ('Dreyer-201','projector');
INSERT INTO Equipment VALUES **('Zuse-127','projector');**
INSERT INTO Equipment VALUES ('Shannon-164','projector');
INSERT INTO Equipment VALUES ('Dreyer-201','whiteboard');
INSERT INTO Equipment VALUES **('Zuse-127','whiteboard');**
INSERT INTO Equipment VALUES ('Shannon-164','whiteboard');

这是我的答案：

SELECT DISTINCT room, type
FROM Equipment
WHERE type = 'projector' AND NOT type = 'whiteboard'

它正在工作-但我的输出列出了Zuse-127房间。我知道投影机和白板都可以单独使用它，但是如何解决此问题？因此，Zuse-127房间没有显示。

最佳答案

对于属于白板的房间，尝试使用此功能NOT IN()

SELECT DISTINCT room, type
FROM Equipment
WHERE type = 'projector' AND room
 NOT IN (SELECT `room` FROM Equipment WHERE type = 'whiteboard' )

关于mysql - mysql查询输出投影仪和白板，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/18879524/