面试的时候被面试官一道SQL题给难住了

背景是这样的，有一个朋友去面试，被面试官出的题难到了，然后根据好友的描述总结了一篇文章，希望大家面试的时候不再踩坑

建表语句和模拟数据

books 表

建表语句

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);

sales 表

建表语句

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);

要求1

查找销售数量最多的书籍的标题、作者和销售数量。

分析要求

题目分析：
我们先分析一下面试官给的要求

1. 首先，我们需要对sales表进行分组，按book_id计算总销售数量。
2. 然后，我们需要将上一步的结果与books表连接，以获取书籍的标题和作者。
3. 最后，我们需要对上一步的结果按销售数量降序排序，并只返回销售数量最多的书籍。

答案SQL

-- 计算每本书的总销售数量  
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;  

分析答案

1. book_sales CTE计算了每本书的总销售数量。
2. max_sales_book CTE找到了销售数量最多的书籍ID。
3. 最后的SELECT语句将上述两个CTE与books表连接，并返回了所需的信息。

查询结果

title	author	total_quantity
1984	George Orwell	9

要求2

查找每个作者销售总额最高的书籍的标题、作者和销售总额。如果有多个书籍销售总额相同且最高，则只需返回其中一个。

分析要求

我们还是先分析一下给出的要求

1. 首先，我们需要对sales表和books表进行连接，并按作者和书籍ID分组，计算每本书的销售总额。
2. 然后，我们需要对每个作者的销售总额进行排名，只保留排名最高的书籍（如果有多个书籍销售总额相同，则只保留一个）。
3. 最后，我们返回书籍的标题、作者和销售总额。

答案SQL

-- 计算每本书的销售总额  
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;  

分析答案

1. book_author_sales CTE计算了每本书的销售总额。
2. ranked_book_author_sales CTE对每个作者的销售总额进行了排名。
3. 最后的SELECT语句返回了排名最高的书籍的标题、作者和销售总额。

结果

author	title	total_sales
Aldous Huxley	Brave New World	38.97
F. Scott Fitzgerald	The Great Gatsby	99.95
George Orwell	1984	89.91
Harper Lee	To Kill a Mockingbird	59.96
Herman Melville	Moby Dick	50.97
J.D. Salinger	The Catcher in the Rye	13.99
Jane Austen	Pride and Prejudice	35.97
Leo Tolstoy	War and Peace	99.96