CREATE TABLE books (  
    book_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '书籍ID',  
    title VARCHAR(255) NOT NULL COMMENT '书籍标题',  
    author VARCHAR(255) NOT NULL COMMENT '作者',  
    genre VARCHAR(50) NOT NULL COMMENT '书籍类型',  
    price DECIMAL(10, 2) NOT NULL COMMENT '书籍价格',  
    publish_date DATE NOT NULL COMMENT '出版日期',  
    stock_quantity INT NOT NULL COMMENT '库存数量'  
) COMMENT='书籍信息表';

-- 模拟数据  
INSERT INTO books (title, author, genre, price, publish_date, stock_quantity) VALUES  
('The Great Gatsby', 'F. Scott Fitzgerald', 'Fiction', 19.99, '1925-04-10', 100),  
('1984', 'George Orwell', 'Dystopian', 9.99, '1949-06-08', 50),  
('To Kill a Mockingbird', 'Harper Lee', 'Fiction', 14.99, '1960-07-11', 75),  
('Brave New World', 'Aldous Huxley', 'Science Fiction', 12.99, '1932-05-14', 80),  
('Moby Dick', 'Herman Melville', 'Fiction', 16.99, '1851-11-14', 60),  
('Pride and Prejudice', 'Jane Austen', 'Romance', 11.99, '1813-01-28', 90),  
('War and Peace', 'Leo Tolstoy', 'Historical Fiction', 24.99, '1869-01-01', 40),  
('The Catcher in the Rye', 'J.D. Salinger', 'Fiction', 13.99, '1951-07-16', 120);

CREATE TABLE sales (  
    sale_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '销售ID',  
    book_id INT NOT NULL COMMENT '书籍ID（外键）',  
    quantity INT NOT NULL COMMENT '销售数量',  
    sale_date DATE NOT NULL COMMENT '销售日期',  
    customer_id INT NOT NULL COMMENT '客户ID',  
    FOREIGN KEY (book_id) REFERENCES books(book_id)  
) COMMENT='销售记录表';

-- 模拟数据  
INSERT INTO sales (book_id, quantity, sale_date, customer_id) VALUES  
(1, 2, '2023-01-01', 101),  
(2, 1, '2023-01-02', 102),  
(3, 3, '2023-01-03', 103),  
(1, 1, '2023-01-04', 104),  
(4, 2, '2023-01-05', 105),  
(2, 5, '2023-01-06', 101),  
(3, 1, '2023-01-07', 106),  
(5, 3, '2023-01-08', 102),  
(6, 2, '2023-01-09', 107),  
(7, 4, '2023-01-10', 108),  
(8, 1, '2023-01-11', 109),  
(1, 2, '2023-01-12', 110),  
(4, 1, '2023-01-13', 101),  
(2, 3, '2023-01-14', 102),  
(6, 1, '2023-01-15', 111);

-- 计算每本书的总销售数量  
WITH book_sales AS (  
    SELECT  
        book_id,  
        SUM(quantity) AS total_quantity  
    FROM  
        sales  
    GROUP BY  
        book_id  
),  
-- 找到销售数量最多的书籍ID  
max_sales_book AS (  
    SELECT  
        book_id  
    FROM  
        book_sales  
    ORDER BY  
        total_quantity DESC  
    LIMIT 1  
)  
-- 获取书籍的详细信息  
SELECT  
    b.title,  
    b.author,  
    bs.total_quantity  
FROM  
    books b  
JOIN  
    book_sales bs ON b.book_id = bs.book_id  
JOIN  
    max_sales_book msb ON b.book_id = msb.book_id;

-- 计算每本书的销售总额  
WITH book_author_sales AS (  
    SELECT  
        b.author,  
        b.title,  
        SUM(s.quantity * b.price) AS total_sales  
    FROM  
        books b  
    JOIN  
        sales s ON b.book_id = s.book_id  
    GROUP BY  
        b.author, b.title  
),  
-- 对每个作者的销售总额进行排名  
ranked_book_author_sales AS (  
    SELECT  
        *,  
        RANK() OVER (PARTITION BY author ORDER BY total_sales DESC) AS sales_rank  
    FROM  
        book_author_sales  
)  
-- 返回排名最高的书籍  
SELECT  
    author,  
    title,  
    total_sales  
FROM  
    ranked_book_author_sales  
WHERE  
    sales_rank = 1;