不改表结构：关联三表查询各学生最高价格图书详情

Alright, let's solve this problem where we need to fetch the highest-priced book details for each student, using the existing three tables without modifying their structure. Here are two reliable approaches you can use:

Approach 1: Using Window Functions (Recommended) #

Window functions like ROW_NUMBER() or RANK() are perfect for this scenario because they let us rank records within each student's group easily.

SQL Query #

WITH ranked_books AS (
    SELECT
        s.student_id,
        s.student_name,
        bp.price,
        bd.book_id,
        bd.book_name,
        bd.book_image,
        -- Rank books by price descending for each student
        ROW_NUMBER() OVER (PARTITION BY s.student_id ORDER BY bp.price DESC) AS price_rank
    FROM student s
    JOIN book_price bp ON s.student_id = bp.student_id
    JOIN book_data bd ON bp.book_id = bd.book_id
)
SELECT
    student_id,
    student_name,
    price AS highest_book_price,
    book_id,
    book_name,
    book_image
FROM ranked_books
WHERE price_rank = 1;

Explanation #

• CTE ranked_books: We first join all three tables to combine student info, book prices, and book details. The ROW_NUMBER() function assigns a unique rank to each book per student, sorted by price from highest to lowest.
• Final Selection: We filter for records where price_rank = 1 to get only the top-priced book for each student.

Note on Ties #

If a student has multiple books with the exact same highest price, ROW_NUMBER() will pick just one (arbitrarily). If you want to include all tied books, replace ROW_NUMBER() with RANK() instead—it will assign the same rank to all books with the maximum price.

Approach 2: Using Subqueries (For Older SQL Versions) #

If your database doesn't support window functions, you can use a subquery to find the maximum price per student, then join back to get the details.

SQL Query #

SELECT
    s.student_id,
    s.student_name,
    bp.price AS highest_book_price,
    bd.book_id,
    bd.book_name,
    bd.book_image
FROM student s
JOIN book_price bp ON s.student_id = bp.student_id
JOIN book_data bd ON bp.book_id = bd.book_id
JOIN (
    -- Subquery to get max price per student
    SELECT student_id, MAX(price) AS max_price
    FROM book_price
    GROUP BY student_id
) max_prices ON bp.student_id = max_prices.student_id AND bp.price = max_prices.max_price;

Explanation #

• Subquery max_prices: This calculates the highest price each student paid for any book.
• Joins: We join this subquery back to the main tables to fetch only the books that match the student's maximum price. This will return all tied books if there are any (unlike ROW_NUMBER() in Approach 1).

Example Output #

Using your sample data (e.g., Sarah has books priced at $20, $35; John has $15, $35; Peter has $25), the output would look like:

内容的提问来源于stack exchange，提问作者Myroslav Tedoski