mysql - 我找不到包含特定数量书籍的学生姓名

This question already has answers here:

Select rows for a specific date using TIMESTAMP datatype
                                
                                    （3个答案）
                                
                        
                                5个月前关闭。
            
                    
我的数据库中有以下表格：

Student    (Stud_no: string, Stud_name: string)
Membership (Mem_no: string, Stud_no: string)
Book       (book_no: string, book_name: string, author: string)
Iss_rec    (iss_no: integer, iss_date: date, Mem_no: string, book_no: string)

我创建了表并插入了值。

现在，我必须列出达到借阅限制3的学生（即，没有人可以借阅超过3本书）

我写了这个查询：

SELECT student.stud_name, COUNT(*) AS 'Number of Books'
FROM student, iss_rec, membership, book
WHERE student.stud_no = membership.stud_no
AND membership.Mem_no = iss_rec.Mem_no
AND book.book_no = iss_rec.book_no
GROUP BY student.stud_name

它显示每个学生借的书的数量。但是，只有三本书我怎么才能找到名字呢？
另外，我应该采取什么措施才能使那些拥有3本书的学生不再借书？

最佳答案

对于汇总结果，您可以使用具有用于过滤的值

SELECT student.stud_name, COUNT(*) AS 'Number of Books'
FROM student, iss_rec, membership, book
WHERE student.stud_no = membership.stud_no
AND membership.Mem_no = iss_rec.Mem_no
AND book.book_no = iss_rec.book_no
GROUP BY student.stud_name
having  COUNT(*) > 3

或三本书

SELECT student.stud_name, COUNT(*) AS 'Number of Books'
FROM student, iss_rec, membership, book
WHERE student.stud_no = membership.stud_no
AND membership.Mem_no = iss_rec.Mem_no
AND book.book_no = iss_rec.book_no
GROUP BY student.stud_name
having  COUNT(*) =  3

关于mysql - 我找不到包含特定数量书籍的学生姓名，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/57606538/