面试题：MariaDB虚拟列与复杂查询场景的结合

Question

有一个图书馆管理系统，包含书籍表books（book_id, title, price, pages）和借阅记录表borrow_records（record_id, book_id, borrow_date, return_date）。在MariaDB中，如何利用虚拟列来优化查询：找出每本被借阅过的书籍，按照其借阅次数的比例与总页数的乘积进行排序展示。请详细说明创建虚拟列以及编写查询的步骤。

Answer 1

1. 创建包含虚拟列的新表：
   • 首先，假设我们要创建一个新表books_with_borrow_stats来存储带有虚拟列的数据。
   • 虚拟列的计算逻辑是：借阅次数的比例（某本书借阅次数 / 总借阅次数）与总页数的乘积。
   • 以下是创建表的SQL语句：

CREATE TABLE books_with_borrow_stats AS
SELECT 
    b.book_id,
    b.title,
    b.price,
    b.pages,
    (SELECT COUNT(*) FROM borrow_records br WHERE br.book_id = b.book_id) / (SELECT COUNT(*) FROM borrow_records) * b.pages AS borrow_ratio_pages
FROM 
    books b
WHERE 
    EXISTS (SELECT 1 FROM borrow_records br WHERE br.book_id = b.book_id);

• 在上述语句中：
  • (SELECT COUNT(*) FROM borrow_records br WHERE br.book_id = b.book_id)计算每本书的借阅次数。
  • (SELECT COUNT(*) FROM borrow_records)计算总的借阅次数。
  • (SELECT COUNT(*) FROM borrow_records br WHERE br.book_id = b.book_id) / (SELECT COUNT(*) FROM borrow_records) * b.pages计算借阅次数比例与总页数的乘积，并将其作为虚拟列borrow_ratio_pages。
  • WHERE EXISTS (SELECT 1 FROM borrow_records br WHERE br.book_id = b.book_id)确保只选择被借阅过的书籍。

2. 查询展示：
   • 一旦创建了books_with_borrow_stats表，查询按照borrow_ratio_pages排序展示的SQL语句如下：

SELECT 
    book_id,
    title,
    price,
    pages,
    borrow_ratio_pages
FROM 
    books_with_borrow_stats
ORDER BY 
    borrow_ratio_pages DESC;

• 上述查询从books_with_borrow_stats表中选择所有列，并按照borrow_ratio_pages虚拟列的值进行降序排序展示。

注意：在实际应用中，如果原表数据经常变动，可能需要考虑使用触发器等机制来更新虚拟列的值，以保证数据的准确性。