如何在SQL中获取每组的最后一条记录

问题描述：

我正面临一个相当有趣的问题。我有一个表结构如下：如何在SQL中获取每组的最后一条记录

CREATE TABLE [dbo].[Event] 
(
    Id int IDENTITY(1,1) NOT NULL, 
    ApplicationId nvarchar(32) NOT NULL, 
    Name nvarchar(128) NOT NULL, 
    Description nvarchar(256) NULL, 
    Date nvarchar(16) NOT NULL, 
    Time nvarchar(16) NOT NULL, 
    EventType nvarchar(16) NOT NULL, 
    CONSTRAINT Event_PK PRIMARY KEY CLUSTERED (Id) WITH (
     PAD_INDEX = OFF, 
     STATISTICS_NORECOMPUTE = OFF, 
     IGNORE_DUP_KEY = OFF, 
     ALLOW_ROW_LOCKS = ON, 
     ALLOW_PAGE_LOCKS = ON 
    ) 
)

所以问题是，我必须在网格中显示此数据。有两个要求。第一个是显示所有事件，而不管应用程序抛出它们。这很简单 - 选择语句将很容易地完成这项工作。

第二个要求是能够按Application对事件进行分组。换句话说，以一种方式显示所有事件，如果ApplicationId重复多次，则只抓取每个应用程序的最后一个条目。这个事件（Id）的主键在这个查询/视图中不再需要。

您可能还注意到事件日期和时间是字符串格式。这是可以的，因为它们遵循标准的日期时间格式：mm/dd/yyyy和hh：mm：ss。我可以拉这些如下：

Convert(DateTime, (Date + ' ' + Time)) AS 'TimeStamp'

我的问题是，如果我使用的列的其余聚合函数，我不知道他们会怎么表现：

SELECT 
    ApplicationId, 
    MAX(Name), 
    MAX(Description), 
    MAX(CONVERT(DateTime, (Date + ' ' + Time))) AS 'TimeStamp', 
    MAX(EventType) 
FROM 
    Event 
GROUP BY 
    ApplicationId

我的原因犹豫是因为MAX这样的函数会从（子）记录集中返回给定列的最大值。它不需要拉最后的记录！

关于如何在每个应用程序的基础上只选择最后一条记录的想法？

使用窗口函数（在Oracle中，所以像row_number（）over（partition by ...），AFAIK SQL服务器具有类似的功能。 – 2011-06-01 12:42:59

答

您可以使用ranking function和common table expression。

WITH e AS 
(
    SELECT *, 
     ROW_NUMBER() OVER 
     (
      PARTITION BY ApplicationId 
      ORDER BY CONVERT(datetime, [Date], 101) DESC, [Time] DESC 
     ) AS Recency 
    FROM [Event] 
) 
SELECT * 
FROM e 
WHERE Recency = 1

由于'mm/dd/yyyy'格式不能正确排序为字符串，因此您不能只按日期和时间排序而不转换为日期时间值。 – 2011-06-01 12:56:21

谢谢@Anthony Faull。这工作，但我不明白如何。 – bleepzter 2011-06-01 12:56:54

@damien好的。我已更新ORDER BY子句将美国日期（月 - 日 - 年）转换为可排序的日期。 – 2011-06-01 13:11:56

答

SELECT 
    E.ApplicationId, 
    E.Name, 
    E.Description, 
    CONVERT(DateTime, (E.Date + ' ' + E.Time)) AS 'TimeStamp', 
    E.EventType 
FROM 
    Event E 
    JOIN (SELECT ApplicationId, 
       MAX(CONVERT(DateTime, (Date + ' ' + Time))) AS max_date 
      FROM Event 
     GROUP BY ApplicationId) EM 
     on EM.ApplicationId = E.ApplicationId 
    and EM.max_date = CONVERT(DateTime, (E.Date + ' ' + E.Time)))

答

因为你没有在那里where子句，记录的子集，是所有记录。但是，你正在对我认为错误的列进行最大限度的处理。这个查询会给你你想要的。

Select max(applicationid), name, description, CONVERT(DateTime, (Date + ' ' + Time)) 
from event 
group by name, description, CONVERT(DateTime, (Date + ' ' + Time))

答

您可以使用subqery或CTE表来做到这一点：

;WITH CTE_LatestEvents as (
SELECT 
    ApplicationId,  
    MAX(CONVERT(DateTime, (Date + ' ' + Time))) AS 'LatestTimeStamp', 
FROM 
    Event 
GROUP BY 
    ApplicationId 
) 
SELECT 
    ApplicationId, 
    Name, 
    Description, 
    CONVERT(DateTime, (Date + ' ' + Time))) AS 'TimeStamp', 
    EventType 
FROM 
    Event e 
    Join CTE_LatestEvents le 
     on e.applicationid = le.applicationid 
     and CONVERT(DateTime, (e.Date + ' ' + e.Time))) = le.LatestTimeStamp

答

您可以通过使用子查询与组 - 组通过争论并不需要在选择。这假设Id是一个自动递增的，所以最大的一个是最近的。

SELECT 
    ApplicationId, 
    Name, 
    Description, 
    CONVERT(DateTime, (Date + ' ' + Time)) AS 'TimeStamp', 
    EventType 
FROM 
    Event e 
WHERE 
    Id in (select max(Id) from Event GROUP BY ApplicationId)

答

，我认为它会为许多工作在那里愿意获取最后插入的记录，它应该由组：

SELECT * FROM（SELECT * FROM表名ORDER BY ID DESC）为X GROUP BY字段名

它将以下工作：

表结构 ID名称状态 1朱奈德是 2贾瓦德否 3法赫德是 4朱奈德否 5卡希夫是

结果上述查询 ID名称状态后 4朱奈德否 2贾瓦德否 3法赫德是 4卡希夫是

它只是产生组名称的最后一个记录。

答

由于SQL Server 2012中，你可以简单地

SELECT 
    [Month] 
    , [First] = FIRST_VALUE(SUM([Clicks])) OVER (ORDER BY [Month]) 
    , [Last] = FIRST_VALUE(SUM([Clicks])) OVER (ORDER BY [Month] DESC) 
FROM 
    [dbo].[Table] 
GROUP BY [Month] 
ORDER BY [Month]

当然也有LAST_VALUE – sehe 2014-09-09 14:31:36

答

经过6年的另一个答案为SQL Server：

select t1.[Id], t2.[Value] 
from [dbo].[Table] t1 
    outer apply ( 
    select top 1 [Value] 
     from [dbo].[Table] t2 
     where t2.[Month]=t1.[Month] 
     order by [dbo].[Date] desc 
)

虽然我如PostgreSQL解决方案以其独特的更好的功能，它是更好键入和更有效：

select distinct on (id),val 
from tbl 
order by id,val

如何在SQL中获取每组的最后一条记录

相关推荐