求助：如何通过关联Table2获取Table1中重复Access Number的SQL语句

Got it, let's break down how to complete your SQL statement to meet your exact requirements. Here's the full, working query along with clear explanations:

Final SQL Statement #

SELECT AccessNumber, Value
FROM (
    SELECT DISTINCT 
        a.AccessNumber, 
        b.Value,
        -- Assign a unique row number to each Value entry per AccessNumber
        ROW_NUMBER() OVER (PARTITION BY a.AccessNumber ORDER BY b.Value) AS rn
    FROM Table1 a
    JOIN Table2 b 
        ON a.AccessNumber = b.AccessNumber
    WHERE a.AccessNumber IN (
        -- Subquery to find AccessNumbers with multiple records in Table1
        SELECT AccessNumber
        FROM Table1
        GROUP BY AccessNumber
        HAVING COUNT(*) > 1
    )
) AS ranked_results
WHERE rn = 1;

Why This Works #

Let's break down each component step by step:

• Identify duplicate AccessNumbers in Table1: The inner subquery SELECT AccessNumber FROM Table1 GROUP BY AccessNumber HAVING COUNT(*) > 1 filters for AccessNumbers that appear more than once in Table1, hitting your first condition.
• Link to Table2: The JOIN between Table1 and Table2 ensures we only keep AccessNumbers that exist in both tables, fulfilling your second requirement.
• Eliminate redundant rows: DISTINCT prevents duplicate results caused by multiple matching records in Table1.
• Pick a single Value per AccessNumber: The ROW_NUMBER() window function assigns a unique number to each Value entry for a given AccessNumber (sorted by Value here—you can adjust the ORDER BY clause to match your priority). Filtering where rn = 1 guarantees we get just one Value per qualifying AccessNumber, which matches your expected result of 1000 - Value1000.

Simplified Version (For Single Matching Result) #

If you only expect one qualifying AccessNumber (like in your example), you can use a more concise version without window functions:

SELECT DISTINCT a.AccessNumber, b.Value
FROM Table1 a
JOIN Table2 b ON a.AccessNumber = b.AccessNumber
WHERE a.AccessNumber IN (
    SELECT AccessNumber
    FROM Table1
    GROUP BY AccessNumber
    HAVING COUNT(*) > 1
)
ORDER BY b.Value
LIMIT 1;

This returns the first sorted Value for the qualifying AccessNumber, which in your sample data will be Value1000.

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