根据最新日期获取记录

debugcn 发表于 Dev

比拉

在下面的查询中，我只需要记录一条最新的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，然后对其进行更新。

在此处输入图片说明

希伦·达杜克（Hiren Dhaduk）

使用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