如何在 SQL 中根据 LastCreatedDate 选择不同的名称

Question

我有包含列的示例表：

ID(guid)                                 Name        CreatedDate

B27EB95B-B219-46BD-9E72-A378B3E7FED0    A       2005-11-20 22:49:46.000
33D43825-918D-4DC0-874A-53670563EA03    A       2009-10-03 10:34:00.000
28F26DA6-B144-4C0C-AC2F-4DDD2D74357E    B       2011-03-23 08:08:39.000
1491D95F-BA58-41EE-8982-B713DE9DECD7    C       2006-01-18 14:53:12.000
FD25C05D-0C1A-4314-BCA7-F4F33B0E890D    C       2006-10-05 14:25:58.000
F4256300-CAA6-4E80-8D1B-B89273274088    D       2008-03-12 08:56:32.000

这应该是我在 SSIS 上的 OledbDatasource。

现在的问题是我需要获取具有最后创建日期的不同名称及其在sql中的ID。

这是输出结果：

ID(guid)                                 Name        CreatedDate

33D43825-918D-4DC0-874A-53670563EA03    A       2009-10-03 10:34:00.000
28F26DA6-B144-4C0C-AC2F-4DDD2D74357E    B       2011-03-23 08:08:39.000
047ED9C3-DF92-45D5-B295-EE52184116FB    C           2006-10-05 14:23:40.000
F4256300-CAA6-4E80-8D1B-B89273274088    D       2008-03-12 08:56:32.000

谢谢问候

Answer 1

如果您使用的是 SQL Server 2005 或更高版本，则可以使用排名和CTE：

WITH ranked AS (
  SELECT
    ID, Name, CreatedDate,
    rn = ROW_NUMBER() OVER (PARTITION BY Name ORDER BY CreatedDate DESC)
  FROM atable
)
SELECT
  ID,
  Name,
  CreatedDate
FROM ranked
WHERE rn = 1

Answer 2

SELECT ID, Name, CreatedDate
FROM table JOIN
  (SELECT MAX(CreatedDate) CreatedDate, Name FROM table GROUP BY Name) max_date
USING (Name, CreatedDate)

（将表更改为表的实际名称。）

对于 SQLServer，执行以下操作：

SELECT table.ID, table.Name, table.CreatedDate
FROM table JOIN
  (SELECT MAX(CreatedDate) CreatedDate, Name FROM table GROUP BY Name) max_date
ON table.Name = max_date.name AND table.CreatedDate = max_date.CreatedDate

Answer 3

试试这个

SELECT myTable.Id, myTable.Name, myTable.CreatedDate
FROM table as myTable
INNER JOIN (SELECT Name, MAX(CreatedDate) AS MaxDate FROM table GROUP BY Name) 
    AS MaxRecord ON MaxRecord.Name = myTable.Name 
                AND MaxRecord.MaxDate= myTable.CreatedDate

Answer 4

这将在 SQL Server 上产生您想要的结果：

select distinct x.ID, x.Name, x.CreatedDate
from table t
    cross apply (
        select top 1 ID, Name, CreatedDate
        from table t1 
        where t1.Name = t.Name
        order by CreatedDate desc
    ) x
order by x.Name;