SQL Server：按客户与产品代码分组获取最新成交单价

Hey there! Let's figure out how to pull the most recent sale price for each customer and product combination from your sales order line table. First, let's assume your table is named sales_order_lines with these core columns:

• customer (the customer's name/unique ID)
• product_code (the unique identifier for each product)
• unit_price (the price the product was sold for)
• sale_date (the date the transaction happened)

Here are a few reliable approaches tailored to different database systems and business needs:

1. Use Window Functions (Modern SQL Databases) #

This is the cleanest and most efficient method if your database supports window functions (think MySQL 8+, PostgreSQL, SQL Server, etc.). We’ll use ROW_NUMBER() to rank sales for each customer-product pair by date (newest first), then grab the top-ranked entry.

WITH ranked_sales AS (
    SELECT
        customer,
        product_code,
        unit_price,
        sale_date,
        -- Rank sales per customer-product group, newest first
        ROW_NUMBER() OVER (
            PARTITION BY customer, product_code
            ORDER BY sale_date DESC
        ) AS sale_rank
    FROM sales_order_lines
)
SELECT
    customer,
    product_code,
    unit_price AS latest_unit_price
FROM ranked_sales
WHERE sale_rank = 1;

Quick Notes: #

• If multiple sales for the same customer-product fall on the same latest date, ROW_NUMBER() will randomly pick one entry. If you need to keep all matching entries (e.g., different prices on the same day), swap ROW_NUMBER() with RANK() or DENSE_RANK().
• If your table has a unique order line ID (like order_line_id), add it to the ORDER BY clause to guarantee you get the very latest entry: ORDER BY sale_date DESC, order_line_id DESC.

2. Correlated Subquery (For Older Databases) #

If you’re working with an older database that doesn’t support window functions (like MySQL 5.x), this approach works by first finding the latest sale date for each customer-product pair, then matching back to the original table to get the corresponding price.

SELECT
    s1.customer,
    s1.product_code,
    s1.unit_price AS latest_unit_price
FROM sales_order_lines s1
WHERE s1.sale_date = (
    SELECT MAX(s2.sale_date)
    FROM sales_order_lines s2
    WHERE s2.customer = s1.customer
      AND s2.product_code = s1.product_code
);

Quick Notes: #

• This will return all entries that match the latest date. If you only want one price per customer-product pair, wrap unit_price in MAX() (or MIN(), depending on your needs) and add GROUP BY customer, product_code.

3. GROUP BY + JOIN #

This is a versatile alternative that works across nearly all databases. We first group the data to get the latest sale date for each customer-product pair, then join back to the original table to retrieve the associated price.

SELECT
    s.customer,
    s.product_code,
    s.unit_price AS latest_unit_price
FROM sales_order_lines s
JOIN (
    -- Get the latest sale date for each customer-product group
    SELECT
        customer,
        product_code,
        MAX(sale_date) AS latest_sale_date
    FROM sales_order_lines
    GROUP BY customer, product_code
) latest_dates 
    ON s.customer = latest_dates.customer
    AND s.product_code = latest_dates.product_code
    AND s.sale_date = latest_dates.latest_sale_date;

All these methods will deliver the result you’re after—like your example where Brian gets 10 for product ABC, Gary gets 20, and Sam gets 50. Pick the one that fits your database version and business rules!

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