我有一个 SQL 数据库“persons”,它具有基于 IDNum 列的重复条目。我需要查询条目并仅根据最新创建日期显示行或重复条目。
这是查询:
SELECT IDNum
,PersonPGUID
,CreatedDateTime
,FirstName
,MiddleName
,LastName
FROM [Persons]
WHERE IDNum in (298928, 509520, 528203);
它返回这个:
IDNum PersonPGUID CreatedDateTime FirstName MiddleName LastName
298928 C43DEB67-EB04-4066-A374-B8369D508CF6 2007-10-22 10:13:45.000 Jonathan McKinley Bennett-Tisdell
298928 75B63500-7C67-4B54-A2C1-F74EAF98B861 2007-10-22 10:15:35.000 Jonathan McKinley Bennett-Tisdell
509520 57A1FB38-93B5-4D7B-8A17-243EC9330766 2005-02-10 11:55:13.000 Lannon Caleb Morgan
509520 176667F4-318E-415B-AFFC-A80348325A9D 2004-08-24 06:26:27.000 Lannon Caleb Morgan
528203 36C372CB-C9AD-4CEC-8553-8147C7FEDE20 2009-06-08 09:26:43.000 Pedro Trigueros
528203 5B71C0D5-10EB-4375-8F80-E8F01381E08A 2011-12-15 10:28:11.000 Pedro Trigueros
我需要过滤以仅显示重复项,并且它需要是每组重复项的最大日期记录。
谢谢,
威廉
试试这个内连接。它会给你带来良好的性能,因为使用了内联视图。
SELECT [Persons].*
FROM [Persons]
INNER JOIN
(
SELECT
IDNum,
MAX(CreatedDateTime) AS CreatedDateTime
FROM [Persons]
WHERE IDNum in (298928, 509520, 528203)
GROUP BY IDNum
HAVING COUNT(*) > 1
) latest_records
ON [Persons].IDNum = latest_records.IDNum AND [Persons].CreatedDateTime = latest_records.CreatedDateTime;
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)