SELECT含多字段时无法单字段GROUP BY的图书集合查询SQL问题

Let's break down what's going wrong with your current query and fix it step by step, matching all your requirements.

First, Diagnose the Original Issues #

Your query has a few critical problems that are causing errors and incorrect results:

• Invalid subquery table reference: You used b2 in the subquery but never declared it (it should be book b2).
• Wrong join condition: o2.id=b2.id is incorrect—you need to link owners to their books via b2.owner_id = o2.id, since owner_id is the foreign key connecting the two tables.
• Group by violation: In strict SQL modes (like PostgreSQL or MySQL with ONLY_FULL_GROUP_BY), every non-aggregated column in your SELECT clause must be included in the GROUP BY clause. Including b.* and o.address without grouping by them triggers the error you're seeing.
• Unintended Cartesian product: The outer query doesn't properly join owner and book, which would create duplicate, unrelated records instead of linking the owner to their books.

Solution 1: Aggregate Borrow Status First, Then Join to Volume 1 Records #

This approach first calculates the borrow status for each collection (whether any volume can be borrowed), then joins to the first volume of each collection for the target owner, and pulls in the owner's address.

SELECT
    o.address,
    b.id AS book_id,
    b.author,
    b.title,
    b.isbn,
    b.collection_name,
    b.collection_id,
    b.volume_number,
    b.owner_id,
    collection_borrow_status.can_be_borrowed
FROM owner o
-- Join to the owner's books that are volume 1
JOIN book b 
    ON o.id = b.owner_id 
    AND b.volume_number = 1
-- Join to a subquery that gets the borrow status for each collection of the owner
JOIN (
    SELECT
        collection_name,
        BOOL_OR(can_be_borrowed) AS can_be_borrowed
    FROM book
    WHERE owner_id = ${owner_id}
    GROUP BY collection_name
) AS collection_borrow_status
    ON b.collection_name = collection_borrow_status.collection_name
-- Ensure one record per collection (in case multiple volume 1 entries exist)
GROUP BY 
    o.address,
    b.id,
    b.author,
    b.title,
    b.isbn,
    b.collection_name,
    b.collection_id,
    b.volume_number,
    b.owner_id,
    collection_borrow_status.can_be_borrowed

Solution 2: Use Window Functions for Cleaner Results #

Window functions let us calculate the borrow status across the entire collection while filtering for the first volume, avoiding a separate subquery join:

SELECT DISTINCT
    o.address,
    b.id AS book_id,
    b.author,
    b.title,
    b.isbn,
    b.collection_name,
    b.collection_id,
    b.volume_number,
    b.owner_id,
    BOOL_OR(b.can_be_borrowed) OVER (PARTITION BY b.collection_name) AS can_be_borrowed
FROM owner o
JOIN book b 
    ON o.id = b.owner_id
WHERE 
    b.owner_id = ${owner_id}
    AND b.volume_number = 1

The DISTINCT clause ensures we only return one record per collection (even if multiple volume 1 entries exist for the same collection). The BOOL_OR window function computes the borrow status across all volumes in the collection, so it will return true if any volume is borrowable.

Key Compliance with Your Requirements #

1. Only returns Volume 1: Both queries filter for volume_number = 1.
2. Single record per collection: GROUP BY (Solution 1) or DISTINCT (Solution 2) eliminates duplicates.
3. Borrow status reflects entire collection: BOOL_OR(can_be_borrowed) correctly returns true if any volume in the collection is borrowable.
4. Includes owner address: Proper joins to the owner table pull in the required address field.

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