请求优化SQL Server查询：合并多语句为单查询获取ppp1结果

Got it, let's break down what your original script is doing first, then rewrite it into a single query without using intermediate tables.

Your original two-step process:

• First, you filter out primary care providers (IS_PCP=1) for patients in your CareWire0521 dataset, then group by patient and provider details to create pat_primary_provider.
• Then you handle cases where a patient might have multiple PCPs by picking the one with the highest Provider_ID to create ppp1.

Here's the single-query version that skips the intermediate tables and directly returns the same result as ppp1:

WITH ranked_providers AS (
    SELECT 
        pp.pat_key,
        pp.PROV_NPI AS Provider_ID,
        CONCAT(pp.LAST_NM, ' ', pp.FIRST_NM) AS Provider_Name,
        ROW_NUMBER() OVER (PARTITION BY pp.pat_key ORDER BY pp.PROV_NPI DESC) AS rn
    FROM TRDW.dbo.PATIENT_PROVIDER pp
    WHERE pp.IS_PCP = 1 
        AND pp.pat_key IN (SELECT Consumer_ID FROM CareWire0521)
)
SELECT pat_key, Provider_ID, Provider_Name
FROM ranked_providers
WHERE rn = 1;

Alternatively, if you prefer to avoid CTEs (though CTEs make the logic cleaner here), you can use a subquery instead:

SELECT pat_key, Provider_ID, Provider_Name
FROM (
    SELECT 
        pp.pat_key,
        pp.PROV_NPI AS Provider_ID,
        CONCAT(pp.LAST_NM, ' ', pp.FIRST_NM) AS Provider_Name,
        ROW_NUMBER() OVER (PARTITION BY pp.pat_key ORDER BY pp.PROV_NPI DESC) AS rn
    FROM TRDW.dbo.PATIENT_PROVIDER pp
    WHERE pp.IS_PCP = 1 
        AND pp.pat_key IN (SELECT Consumer_ID FROM CareWire0521)
) AS sub
WHERE rn = 1;

How this works: #

• The inner query (or CTE) adds a row number (rn) to each provider record for a patient, ordered by PROV_NPI in descending order. This ensures the provider with the highest Provider_ID gets assigned rn = 1.
• We then filter to only keep rows where rn = 1, which gives you exactly the same output as your original two-step process.

This approach is more efficient because it avoids writing intermediate tables to disk, and keeps all your logic consolidated in one readable block.

内容的提问来源于stack exchange，提问作者D. Koumatos