图书馆多表查询例子五个
时间: 2023-08-11 07:10:08 浏览: 43
当然,以下是五个图书馆多表查询的例子:
1. 查询每个类别拥有的图书数量,按照数量从高到低排序。
```
SELECT category.name, COUNT(book.id) AS book_count
FROM category
LEFT JOIN book ON category.id = book.category_id
GROUP BY category.id
ORDER BY book_count DESC;
```
2. 查询每个作者写过的图书数量,并按照数量从高到低排序。
```
SELECT author.name, COUNT(book.id) AS book_count
FROM author
LEFT JOIN book_author ON author.id = book_author.author_id
LEFT JOIN book ON book_author.book_id = book.id
GROUP BY author.id
ORDER BY book_count DESC;
```
3. 查询每个用户借阅的图书信息,包括图书名称、作者、借阅时间和归还时间。
```
SELECT user.name, book.name, author.name, borrow.borrow_date, borrow.return_date
FROM user
LEFT JOIN borrow ON user.id = borrow.user_id
LEFT JOIN book ON borrow.book_id = book.id
LEFT JOIN book_author ON book.id = book_author.book_id
LEFT JOIN author ON book_author.author_id = author.id
ORDER BY borrow.borrow_date DESC;
```
4. 查询借阅次数最多的前10本图书的名称和借阅次数。
```
SELECT book.name, COUNT(borrow.id) AS borrow_count
FROM book
LEFT JOIN borrow ON book.id = borrow.book_id
GROUP BY book.id
ORDER BY borrow_count DESC
LIMIT 10;
```
5. 查询借阅时间超过30天的图书信息,包括图书名称、作者、借阅时间和归还时间。
```
SELECT book.name, author.name, borrow.borrow_date, borrow.return_date
FROM book
LEFT JOIN book_author ON book.id = book_author.book_id
LEFT JOIN author ON book_author.author_id = author.id
LEFT JOIN borrow ON book.id = borrow.book_id
WHERE DATEDIFF(borrow.return_date, borrow.borrow_date) > 30;
```