如何在子查询中包含Join，获取重复记录的最新日期时间？

Got it, let's solve this problem by integrating your JOIN into the CTE structure to fetch the latest datetime records for duplicate entries (the ones in your blue box).

Step-by-Step SQL Query #

Here's the adjusted query that combines your JOIN with the CTE and row numbering logic:

WITH p AS (
    -- First, integrate your original JOIN into the base CTE
    SELECT 
        om.*,
        oml.*
    FROM OCCONTROLMERCADERIA om
    JOIN OCCONTROLMERCADERIALINEAS oml 
        ON om.occontrolid = oml.occontrolid
    WHERE om.OCControlNroId = 5519337
),
p_rnk AS (
    SELECT 
        *,
        -- Assign row numbers partitioned by your duplicate identifier, ordered by datetime descending
        ROW_NUMBER() OVER (
            PARTITION BY oml.MercaderiaId  -- Replace this with the column that defines duplicates (e.g., your blue box shared field)
            ORDER BY om.RegistroFecha DESC  -- Replace with your actual datetime column (latest records get row number 1)
        ) AS rnk
    FROM p
)
-- Filter to get only the latest record per duplicate group
SELECT *
FROM p_rnk
WHERE rnk = 1;

Key Explanations #

• CTE p: This replaces your original standalone OCCONTROLMERCADERIA query with the full JOIN, pulling all related records for OCControlNroId=5519337 in one place.
• CTE p_rnk: The ROW_NUMBER() function is the core here:
  • PARTITION BY: Group records by the column that identifies duplicates (e.g., if your blue box records share the same product ID, use that column here).
  • ORDER BY ... DESC: Sort each group by your datetime column in descending order, so the most recent record gets a row number of 1.
• Final SELECT: Filters for rnk=1 to grab only the latest record from each duplicate group (exactly the blue box records you need).

Important Notes #

Make sure to replace:

• oml.MercaderiaId with the actual column that defines your duplicate records (the common value in your blue box entries).
• om.RegistroFecha with the datetime column in your table that tracks when the record was created/updated.

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