SQL:选择与某个特定人员借过相同书籍的人员的姓名
SQL:选择与某个特定人员借过相同书籍的人员的姓名
提问于 2020-11-28 00:55:46
我在图书馆数据库中有以下两个表:读者(ReaderID，姓氏)借出的(BookID，ReaderID)我需要找到所有借过与读者相同的书的读者，他们的姓氏是‘ReaderID’，并且只打印他们的姓氏。以下代码是我到目前为止所拥有的代码。我认为它正确地计算了相同的图书数量，但我无法让它计算l2表中“John”的条目，以便将该计数与其他读者共同拥有的图书数量进行比较。不允许任何子查询/子选择。
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回答 1
Stack Overflow用户
发布于 2020-11-28 07:42:08
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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/65041039
复制相关文章
相似问题
腾讯云开发者
