使用NOT来选择所有成员

Question

我正在尝试使用MySQL为库数据库编写一个SQL查询。有两个表shelf(studentnumber, booknumber)和booklist(booknumber,booktitle,language)。书单表条目中有4种不同的语言，即italian, spanish, hungarian, german。

我想学习用所选语言读过所有书籍的studentnumbers 。

表的示例数据：

create table shelf(studentnumber INT, booknumber INT); 

INSERT INTO shelf values(1,1),(1,2),(1,3),(2,1),(2,3)(2,4),(2,5),
                        (2,6),(3,6),(3,7)(3,8),(3,9); 

create table booklist(booknumber INT, booktitle VARCHAR(50), language VARCHAR(10); 

INSERT INTO booklist values(1, 'FirstBook', 'italian'),(2,'SecondBook', 'spanish'),
(3,'ThirdBook','italian'),(4,'FourthBook','german'),(5,'FifthBook','german'),
(6, 'SixthBook','spanish'),(7,'SeventhBook','hungarian'),(8,'EightBook','hungarian'),
(9,'NinthBoo‌​k','hungarian'),(10,'TenthBook','Spanish'),(11,'EleventhBook', 'italian');

示例输出:当你看书架和书单表时，你会发现第二个学生用德语读所有的书，第三个学生用匈牙利语阅读所有的书。但是没有一个学生读过所有的意大利语或西班牙语的书。

代码的最后一部分将如下面所写，但我无法构造第一部分，它可能会包括NOT EXISTS从图书列表中选择书号，其中language=‘意大利语’；

Answer 1

这对你来说应该很好：

select studentnumber 
from shelf s1 join booklist b1 on s1.booknumber=b1.booknumber
group by studentNumber, language
having count(language) >= (
        select count(*) from booklist b2
        where b1.language=b2.language
        group by b2.language)

输出：

2
3

您还可以添加第一个select语句语言和count(*)，这将为您提供更多的信息(他们已经阅读的语言以及书籍的数量)。

select studentnumber,language,count(*) 
//rest of code

输出

2, german, 2
3, hungarian, 3

更新

要显示所有用指定语言阅读过书籍的学生(按照注释中的要求)，只需添加一个WHERE子句：

select studentnumber
from shelf s1 join booklist b1 on s1.booknumber=b1.booknumber
where language='german'
group by studentNumber, language
having count(language) >= (
        select count(*) from booklist b2
        where b1.language=b2.language
        group by b2.language)

输出：

2