清理 SQL Server 脚本

Question

SELECT pp.pat_key, MAX(pp.PROV_NPI) [Provider_ID], CONCAT(pp.LAST_NM,' ',pp.FIRST_NM) [Provider_Name]
INTO pat_primary_provider
FROM TRDW.dbo.PATIENT_PROVIDER pp
WHERE IS_PCP=1
AND pat_key IN (SELECT Consumer_ID FROM CareWire0521)
GROUP BY pp.PAT_KEY, pp.last_nm, pp.FIRST_NM;
SELECT ppp.*
INTO ppp1
FROM (SELECT PAT_KEY, MAX(provider_ID) AS maxprov FROM pat_primary_provider GROUP BY PAT_KEY) AS x 
INNER JOIN pat_primary_provider AS ppp ON ppp.PAT_KEY = x.PAT_KEY AND ppp.Provider_ID = x.maxprov; 

我需要在 SQL Server 中仅使用一个查询(无INTO语句(获取ppp1的结果。请帮忙。

Answer 1

只需将第一个查询放入 CTE 中(不带 INTO 子句(。然后从中选择。

;WITH pat_primary_provider AS
(
-- The first query goes here
)
-- The second query goes here

但如下所示的内容也可能返回具有最大PROV_NPI的PAT_KEY：

SELECT TOP 1 WITH TIES
PAT_KEY, 
MAX(PROV_NPI) AS [Max_Provider_ID], 
CONCAT(LAST_NM,' ',FIRST_NM) AS [Patient_Provider_Full_Name]
FROM TRDW.dbo.PATIENT_PROVIDER pp
WHERE IS_PCP = 1
AND PAT_KEY IN (SELECT Consumer_ID FROM CareWire0521)
GROUP BY PAT_KEY, LAST_NM, FIRST_NM
ORDER BY row_number() over (order by MAX(PROV_NPI) desc);

Answer 2

将第一个查询作为子查询插入第二个查询有什么问题？

SELECT ppp.*
FROM (SELECT PAT_KEY, MAX(provider_ID) AS maxprov FROM (SELECT pp.pat_key, MAX(pp.PROV_NPI) [Provider_ID], CONCAT(pp.LAST_NM,' ',pp.FIRST_NM) [Provider_Name]
FROM TRDW.dbo.PATIENT_PROVIDER pp
WHERE IS_PCP=1
AND pat_key IN (SELECT Consumer_ID FROM CareWire0521)
GROUP BY pp.PAT_KEY, pp.last_nm, pp.FIRST_NM) GROUP BY PAT_KEY) AS x 
INNER JOIN (SELECT pp.pat_key, MAX(pp.PROV_NPI) [Provider_ID], CONCAT(pp.LAST_NM,' ',pp.FIRST_NM) [Provider_Name]
FROM TRDW.dbo.PATIENT_PROVIDER pp
WHERE IS_PCP=1
AND pat_key IN (SELECT Consumer_ID FROM CareWire0521)
GROUP BY pp.PAT_KEY, pp.last_nm, pp.FIRST_NM) AS ppp ON ppp.PAT_KEY = x.PAT_KEY AND ppp.Provider_ID = x.maxprov;