在下面的查询中,我只需要记录一条最新的phDate。
WITH TEMP_PAT_PIP_AddedBy
AS (
SELECT S.ppId
,(
SELECT PersonID
FROM [PFMADestination].[dbo].PERSON
WHERE SourceType = 'user_Users'
AND SourceId = S.uuId
) AS AddedByToStore
,phDate
FROM [PFMADestination].[dbo].PatientPIP D
INNER JOIN [PFMAOldProd].[dbo].pat_History S ON S.ppId = D.PatientId
AND phEvent = 'PIP-info uppdaterad'
)
--select * from TEMP_PAT_PIP_AddedBy order by ppId, phDate desc
UPDATE P
SET AddedBy = ISNULL(AddedByToStore, 1), Date=phDate
FROM TEMP_PAT_PIP_AddedBy T
INNER JOIN [PFMADestination].[dbo].[PatientPIP] P ON T.ppId = P.PatientId
CTE的预期输出每个ppId仅获取最新的phDate,如果记录多于1个记录,则忽略其他phDate,然后对其进行更新。
使用partition by子句可以得到预期的输出。尝试以下查询:
WITH TEMP_PAT_PIP_AddedBy
AS (
SELECT S.ppId
,(
SELECT PersonID
FROM [PFMADestination].[dbo].PERSON
WHERE SourceType = 'user_Users'
AND SourceId = S.uuId
) AS AddedByToStore
,phDate , ROW_NUMBER() OVER(PARTITION BY S.ppId ORDER BY phDate DESC) AS NUM
FROM [PFMADestination].[dbo].PatientPIP D
INNER JOIN [PFMAOldProd].[dbo].pat_History S ON S.ppId = D.PatientId
AND phEvent = 'PIP-info uppdaterad'
)
UPDATE P
SET AddedBy = ISNULL(AddedByToStore, 1), Date=phDate
FROM TEMP_PAT_PIP_AddedBy T
INNER JOIN [PFMADestination].[dbo].[PatientPIP] P
ON T.ppId = P.PatientId AND T.NUM=1
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句