对于给定的作者和编辑,我需要列出借用“仅”“所有”该作者和编辑的书籍的借款人的姓名
我尝试了以下方法:
SELECT m.name
FROM MEMBER m,COPIES c,BORROW b1,BOOK b,AUTHOR a,EDITOR e,WRITE w
WHERE m.mid=b1.mid AND b1.copyid=c.copyid AND c.bookid=b.bookid AND b.editor=e.edcode AND a.authorcode=w.author AND w.book=b.bookid
MINUS
(SELECT m.name
FROM MEMBER m,COPIES c,BORROW b1,BOOK b,AUTHOR a,EDITOR e,WRITE w
WHERE m.mid=b1.mid AND b1.copyid=c.copyid AND c.bookid=b.bookid AND b.editor=e.edcode AND a.authorcode=w.author AND w.book=b.bookid AND (a.authorcode<>:P58_AUTHOR OR e.edcode<>:P58_EDITOR)
)
它给了我一个会员,他至少借过一本书,除了那个作者和编辑之外没有借过任何书,所以我需要确保他借了所有的书。
我认为以下内容将为您提供所需的内容:
WITH book_ed_auth_dets AS (SELECT b.bookid,
a.authorcode,
e.edcode,
COUNT(*) OVER (PARTITION BY a.authorcode, e.edcode) tot_num_bks_per_auth_ed
FROM book b
INNER JOIN WRITE w ON b.bookid = w.book
INNER JOIN author a ON w.author = a.authorcode
INNER JOIN editor e ON b.editor = e.edcode
WHERE a.authorcode = :p58_author
AND e.edcode = :p58_editor)
SELECT m.name
FROM MEMBER m
INNER JOIN borrow b1 ON m.mid = b1.mid
INNER JOIN copies ON c.bookid ON b1.copyid = c.copyid
INNER JOIN book_ed_auth_dets bead ON c.bookid = bead.bookid
GROUP BY m.id,
m.name,
bead.authorcode,
bead.edcode
bead.tot_num_bks_per_auth_ed
HAVING COUNT(DISTINCT bead.bookid) = bead.tot_num_bks_per_auth_ed);
NB 未经测试,因为您没有提供要使用的示例数据。
这将查找给定作者和编辑的书籍,并使用分析计数为每一行写出该作者和编辑的书籍总数。
然后我们将成员的借书数与该成员的借书数进行内联,找出不同 bookid 的数量(以防成员多次借同一本书),如果该数量与作者和编辑的图书数量匹配,则仅报告成员。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句