SQL查询：筛选同一number对应不同指定update_date的数据行

Looks like you're trying to track changes for the same number between two specific dates—08.05.18 and 14.05.18. Your current JOIN approach is on the right track, but there are a few tweaks to make it more efficient and avoid unwanted duplicates. Let's break down the solutions:

Solution 1: Side-by-Side Comparison of Changes #

If you want to see the before (08.05.18) and after (14.05.18) records for each number side-by-side, adjust your JOIN to exclude self-matches and avoid duplicate pairs:

SELECT 
    a.id AS old_id, a.number, a.name AS old_name, a.update_date AS old_date,
    b.id AS new_id, b.name AS new_name, b.update_date AS new_date
FROM legal_entity_history a
JOIN legal_entity_history b 
    ON a.number = b.number 
    AND a.id < b.id -- Ensures each pair is returned only once
WHERE 
    a.update_date = '08.05.18' 
    AND b.update_date = '14.05.18';

This query will give you a clear view of how the name (or other fields) changed for each number between the two dates. The a.id < b.id prevents returning the same pair in reverse order (e.g., row 3 paired with row 1 instead of just row 1 paired with row 3).

Solution 2: Fetch All Relevant Rows for Matching Numbers #

If you prefer to see all individual rows for numbers that have entries in both dates (instead of side-by-side), use a subquery to first identify those numbers, then retrieve their records:

SELECT *
FROM legal_entity_history
WHERE 
    number IN (
        SELECT number
        FROM legal_entity_history
        WHERE update_date IN ('08.05.18', '14.05.18')
        GROUP BY number
        HAVING COUNT(DISTINCT update_date) = 2 -- Ensures number exists in both dates
    )
    AND update_date IN ('08.05.18', '14.05.18');

This will return rows 1 and 3 from your sample data, since they share the same number (123) and have the two target dates.

Important Notes: #

• If your update_date is stored as a DATE type (not a string), make sure to use date literals compatible with your SQL dialect (e.g., '2018-05-08' instead of '08.05.18' to avoid format issues).
• Replace number with BIN in the queries if that's the actual column name in your table (your sample uses number but your query uses BIN—just ensure consistency).

内容的提问来源于stack exchange，提问作者A.Dias