如何基于最新date_added去除SQL查询结果中product_id的重复项

Got it, let's work through this problem step by step. First, I spotted a critical issue in your original query: you’re using INNER JOIN ims_order o without an ON clause. That’s going to create a Cartesian product (every row in ims_order_product paired with every row in ims_order), which is almost certainly not what you intended. You need to add a linking condition like i.order_id = o.order_id to properly associate order products with their parent orders—this is essential before fixing the duplicate issue.

Now, to get each unique product_id paired with its most recent date_added, here are three reliable approaches:

1. Use Window Functions (Modern SQL, Most Flexible) #

This method is clean and works if you need to include additional columns later (not just product_id and date_added). We’ll use ROW_NUMBER() to rank records per product, then pick the top-ranked (latest) one:

SELECT product_id, date_added
FROM (
    SELECT 
        i.product_id, 
        o.date_added,
        -- Rank records per product, newest first
        ROW_NUMBER() OVER (PARTITION BY i.product_id ORDER BY o.date_added DESC) AS record_rank
    FROM ims_order_product i
    INNER JOIN ims_order o ON i.order_id = o.order_id -- Fixed join condition
    WHERE i.product_id IN (SELECT p.product_id FROM ims_product p)
) ranked_records
WHERE record_rank = 1; -- Keep only the latest record per product

2. GROUP BY with MAX() (Simplest for Your Exact Use Case) #

If you only need the product_id and its latest date_added (no extra columns), this is the most straightforward option:

SELECT 
    i.product_id, 
    MAX(o.date_added) AS latest_date_added
FROM ims_order_product i
INNER JOIN ims_order o ON i.order_id = o.order_id
WHERE i.product_id IN (SELECT p.product_id FROM ims_product p)
GROUP BY i.product_id; -- Group by product to get the max date per group

3. Correlated Subquery (For Older SQL Databases) #

If you’re working with a database that doesn’t support window functions (like very old versions of MySQL), this approach will get the job done:

SELECT 
    i.product_id, 
    o.date_added
FROM ims_order_product i
INNER JOIN ims_order o ON i.order_id = o.order_id
WHERE 
    i.product_id IN (SELECT p.product_id FROM ims_product p)
    -- Match only the record with the latest date for this product
    AND o.date_added = (
        SELECT MAX(o2.date_added)
        FROM ims_order_product i2
        INNER JOIN ims_order o2 ON i2.order_id = o2.order_id
        WHERE i2.product_id = i.product_id
    );

Quick Note on Your Original WHERE Clause #

The WHERE i.product_id IN (SELECT p.product_id FROM ims_product p) is redundant if ims_order_product.product_id has a foreign key constraint pointing to ims_product.product_id (which it should!). If that constraint exists, you can safely remove this clause to simplify the query.

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