SQL Server嵌套查询需求：从关联分组查询获取对应价格

Got it, let's solve this problem step by step. You want to pair the Price from your full apa_invoice_detail dataset (707 rows) with the grouped results that show the maximum detail_serial per item and group combination (197 rows). Here are two clean ways to achieve this:

Option 1: Using a Subquery #

Wrap your second grouped query as a subquery, then join it back to the original apa_invoice_detail table to fetch the corresponding Price:

SELECT 
    grouped.max_detail_serial,
    grouped.item_name_2,
    grouped.group_name_2,
    detail.Price
FROM (
    -- Your original grouped query, with an alias for the max value
    SELECT 
        MAX(detail_serial) AS max_detail_serial,
        asc_item.item_name_2,
        asc_group.group_name_2
    FROM apa_invoice_detail
    INNER JOIN asc_item ON asc_item.item_id = apa_invoice_detail.item_id
    INNER JOIN asc_group ON asc_group.group_id = asc_item.group_id
    GROUP BY asc_item.item_name_2, asc_group.group_name_2
) AS grouped
-- Join back to get the Price linked to the max detail_serial
INNER JOIN apa_invoice_detail AS detail 
    ON detail.detail_serial = grouped.max_detail_serial;

Option 2: Using a CTE (Common Table Expression) #

If you prefer more readable code, a CTE breaks the logic into separate, named sections:

WITH GroupedDetails AS (
    -- First, define the grouped results with max detail_serial
    SELECT 
        MAX(detail_serial) AS max_detail_serial,
        asc_item.item_name_2,
        asc_group.group_name_2
    FROM apa_invoice_detail
    INNER JOIN asc_item ON asc_item.item_id = apa_invoice_detail.item_id
    INNER JOIN asc_group ON asc_group.group_id = asc_item.group_id
    GROUP BY asc_item.item_name_2, asc_group.group_name_2
)
-- Now join the CTE to the original table to get the Price
SELECT 
    g.max_detail_serial,
    g.item_name_2,
    g.group_name_2,
    d.Price
FROM GroupedDetails g
INNER JOIN apa_invoice_detail d 
    ON d.detail_serial = g.max_detail_serial;

Quick Note #

Make sure detail_serial is unique in apa_invoice_detail. If there are multiple rows with the same maximum detail_serial value (unlikely if it's an auto-incrementing ID or unique identifier), you might get duplicate results. If that's a possibility, you can add additional conditions to the join or use ROW_NUMBER() to pick a specific Price (e.g., the latest one) if needed.

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