sql - SQL：选择与特定人借过相同书籍的人的姓名

Question

我在图书馆数据库中有以下两个表： Readers(ReaderID, Surname) Lended(BookID, ReaderID) 我需要找到所有与姓“John”的读者借阅相同书籍并且只打印他们的姓氏的读者。以下代码是我到目前为止所拥有的。我认为它正确计算了相同书籍的数量，但我无法计算 l2 表中“约翰”的条目，以便将该计数与其他读者共同的书籍进行比较。不允许子查询/子选择。

SELECT DISTINCT  r1.surname     --, COUNT(l2.readerid),COUNT(l1.readerid) -- used to see how many of the
                                                                          -- book ids are the same
FROM readers r1
JOIN lended l1
   ON r1.readerid=l1.readerid
JOIN reader r2
   ON r1.readerid<>r2.readerid 
JOIN lended l2
   ON l2.readerid=r2.readerid  
   AND l2.bookid=l1.bookid
WHERE
(
    (l2.surname='John')
)
GROUP BY r1.surname

示例读者数据：

ReaderID    Surname
1           Lilly
2           John
3           Mike
4           Kelly
5           George

借出数据示例：

BookID    ReaderID
1         1
2         1
3         1
1         2
2         2
1         3
2         3
1         4
4         5
5         5

预期输出：

Mike

对预期输出的补充说明：

John has read books 1 and 2 
Lilly has read books 1, 2 and 3 // one more book compared to John
Kelly has only read book 1 // one less book compared to John
Mike has read books 1 and 2 // exactly the same books as John
George has read books 4 and 5// same amount as John, but not in his list of read books

标签： sql

Answer 1

未经测试的代码，语法是每个 Oracle DB：-

With r1 as (
Select l.bookid 
From reader r , lended l
Where r.readerid = l.readerid
And r.surname =‘John’)
Select r2.surname
From reader r2 , r1, lended l1
Where r2.readerid = l1.readerid
AND l1.bookid = r1.bookid