我有一个应该模仿图书馆管理系统的数据库。我想编写一个查询,提供一个表,该表显示每个发行商的前三本借书,还显示它们的相应排名(因此,从发行人X借书最多的书将显示第1名)。我有一个查询,其中显示以下信息-借书的书名及其对应的出版商,以及每本书的借书次数。如你看到的;布卢姆斯伯里(英国)出席了7次(每本《哈利·波特》一本),但是我希望它仅显示3部关于借阅次数最多的哈利·波特书籍。非常感谢您的帮助。
title | publisher | times
------------------------------------------+------------------------+------
Harry Potter and the Philosopher's Stone | Bloomsbury (UK) | 2
Harry Potter and the Deathly Hallows | Bloomsbury (UK) | 2
Harry Potter the Goblet of Fire | Bloomsbury (UK) | 3
The Fellowship of the Ring | George Allen & Unwin | 1
Calculus | Paerson Addison Wesley | 1
Go Set a Watchman | HarperCollins | 1
Harry Potter the Half-Blood Prince | Bloomsbury (UK) | 4
Harry Potter and the Chamber of Secrets | Bloomsbury (UK) | 3
Harry Potter and Prisoner of Azkaban | Bloomsbury (UK) | 2
Nineteen Eighty-Four | Secker & Warburg | 1
Harry Potter the Order of the Phoenix | Bloomsbury (UK) | 4
To Kill a Mockingbird | J.B.Lippincott & Co | 1
下面的查询将生成上面的视图。
SELECT title,publisher,COUNT(borrowed.resid) AS rank
FROM borrowed
CROSS JOIN book
CROSS JOIN bookinfo
WHERE borrowed.resid = book.resid
AND book.isbn = bookinfo.isbn
AND book.copynumber = borrowed.copynumber
GROUP BY title,publisher;