SQL条件连接问题：返回两条结果时仅保留FR05对应的记录

Hey there! Let's figure out how to fix your query so it only returns the FR05 record instead of both FR27 and FR05 entries. Here are a couple of straightforward solutions depending on your exact needs:

1. Direct Filter (If You Always Want FR05) #

If you know you never need the FR27 record and only care about FR05, the simplest fix is to replace the IN clause with a direct equality check for FR05 in your JOIN condition:

SELECT sad.*, pl.* 
FROM SalesAndDistrib AS sad 
INNER JOIN Price_List AS pl 
    ON pl.VKORGAU = sad.VBAK_VKORG 
    AND sad.VBAP_MATNR = pl.MATNR 
    AND pl.WERKS = 'FR05' -- Target FR05 exclusively

This will only join with rows from Price_List where WERKS is FR05, so you'll never get the FR27 entry.

2. Prioritize FR05 (With FR27 as a Fallback) #

If there are cases where FR05 might not exist for a given VKORGAU and MATNR, and you want to fall back to FR27 when that happens, use a window function to rank records by priority. This ensures you always get FR05 when available, otherwise FR27:

WITH RankedPriceRecords AS (
    SELECT 
        sad.*, 
        pl.*,
        -- Assign rank 1 to FR05, rank 2 to FR27
        ROW_NUMBER() OVER (
            PARTITION BY sad.VBAK_VKORG, sad.VBAP_MATNR
            ORDER BY CASE pl.WERKS WHEN 'FR05' THEN 1 ELSE 2 END
        ) AS record_rank
    FROM SalesAndDistrib AS sad 
    INNER JOIN Price_List AS pl 
        ON pl.VKORGAU = sad.VBAK_VKORG 
        AND sad.VBAP_MATNR = pl.MATNR 
        AND pl.WERKS IN ('FR27', 'FR05')
)
-- Only keep the highest-priority record (rank 1)
SELECT * 
FROM RankedPriceRecords 
WHERE record_rank = 1;

The PARTITION BY clause groups records by the keys you're joining on, so you get one top-priority result per combination of VKORGAU and MATNR.

3. Fallback Join with Subqueries #

Another way to handle the fallback scenario is to first attempt to join with FR05, and if no match is found, join with FR27:

SELECT 
    sad.*,
    -- Use FR05 data if available, else use FR27
    COALESCE(fr05.VKORGAU, fr27.VKORGAU) AS VKORGAU,
    COALESCE(fr05.MATNR, fr27.MATNR) AS MATNR,
    COALESCE(fr05.WERKS, fr27.WERKS) AS WERKS,
    -- Add other Price_List columns you need, using COALESCE for each
    COALESCE(fr05.price, fr27.price) AS price,
    COALESCE(fr05.currency, fr27.currency) AS currency
FROM SalesAndDistrib AS sad
LEFT JOIN Price_List AS fr05
    ON fr05.VKORGAU = sad.VBAK_VKORG 
    AND sad.VBAP_MATNR = fr05.MATNR 
    AND fr05.WERKS = 'FR05'
LEFT JOIN Price_List AS fr27
    ON fr27.VKORGAU = sad.VBAK_VKORG 
    AND sad.VBAP_MATNR = fr27.MATNR 
    AND fr27.WERKS = 'FR27'
-- Ensure we only return rows with at least one match
WHERE fr05.WERKS IS NOT NULL OR fr27.WERKS IS NOT NULL;

This approach gives you explicit control over which columns to pull from each source.

Which Method Should You Choose? #

• Use Method 1 if you always need FR05 and never need FR27.
• Use Method 2 or 3 if you need to prioritize FR05 but want FR27 as a backup when FR05 isn't present.

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