sql - 在SQL Server 2008中使用分组依据和排序依据检索结果时出错

我有一个名为scripts的表，其中包含修改过的过程、函数和表的数据。

CREATE TABLE #Scripts
    (
    ID              NUMERIC (18) IDENTITY NOT NULL,
    [Date]          DATETIME NULL,
    DatabaseName    VARCHAR (50) NULL,
    Name            VARCHAR (100) NULL,
    Type            VARCHAR (20) NULL,
    Action          VARCHAR (50) NULL,
    Description     VARCHAR (500) NULL,
    ModifiedBy      VARCHAR (50) NULL,
    AddedTimestamp  DATETIME NULL,
    UpdateTimestamp DATETIME NULL,
    )
GO

我将记录添加到表中，如下所示。这些只是样本记录。

INSERT INTO #Scripts ([Date], DatabaseName, Name, Type, Action, Description, ModifiedBy, AddedTimestamp, UpdateTimestamp)
VALUES ('2015-01-07 11:16:41.4', 'Test', 'sp_GetData', 'Stored Procedure', 'Created', 'To Get ActivitySubscriptions for Mobile from tblSubscriptions', 'dinesh.alla', '2015-01-07 11:39:39.703', '2015-01-07 11:39:39.703')
GO

INSERT INTO #Scripts ([Date], DatabaseName, Name, Type, Action, Description, ModifiedBy, AddedTimestamp, UpdateTimestamp)
VALUES ('2015-01-07 11:16:41.4', 'Test', 'sp_GetData', 'Stored Procedure', 'Updated', 'To Get ActivitySubscriptions for Mobile from tblSubscriptions', 'dinesh.alla', '2015-01-07 11:39:39.703', '2015-01-07 11:39:39.703')
GO


INSERT INTO #Scripts ([Date], DatabaseName, Name, Type, Action, Description, ModifiedBy, AddedTimestamp, UpdateTimestamp)
VALUES ('2015-01-07 11:16:41.4', 'Test', 'sp_GetData', 'Stored Procedure', 'Deleted', 'To Get ActivitySubscriptions for Mobile from tblSubscriptions', 'dinesh.alla', '2015-01-07 11:39:39.703', '2015-01-07 11:39:39.703')
GO

INSERT INTO #Scripts ([Date], DatabaseName, Name, Type, Action, Description, ModifiedBy, AddedTimestamp, UpdateTimestamp)
VALUES ('2015-01-07 11:16:41.4', 'Test', 'sp_UpdateData', 'Stored Procedure', 'Created', 'To Get ActivitySubscriptions for Mobile from tblSubscriptions', 'dinesh.alla', '2015-01-07 11:39:39.703', '2015-01-07 11:39:39.703')
GO

INSERT INTO #Scripts ([Date], DatabaseName, Name, Type, Action, Description, ModifiedBy, AddedTimestamp, UpdateTimestamp)
VALUES ('2015-01-07 11:16:41.4', 'Test', 'sp_UpdateData', 'Stored Procedure', 'Updated', 'To Get ActivitySubscriptions for Mobile from tblSubscriptions', 'dinesh.alla', '2015-01-07 11:39:39.703', '2015-01-07 11:39:39.703')
GO

INSERT INTO #Scripts ([Date], DatabaseName, Name, Type, Action, Description, ModifiedBy, AddedTimestamp, UpdateTimestamp)
VALUES ('2015-01-07 11:16:41.4', 'Test', 'sp_AddData', 'Stored Procedure', 'Created', 'To Get ActivitySubscriptions for Mobile from tblSubscriptions', 'dinesh.alla', '2015-01-07 11:39:39.703', '2015-01-07 11:39:39.703')
GO

我试着得到如下所示的结果

SELECT MAX(ID) AS ID,MAX(Action) AS Action
FROM #Scripts GROUP BY Name ORDER BY ID ASC

输出：

ID     Action
3      Updated
5      Updated
6      Created

预期产量：

ID     Action
3      Deleted
5      Updated
6      Created

最佳答案

试试这个：

 select distinct sc.action,sc.ID from (
  SELECT MAX(ID)  OVER(PARTITION BY NAME  ) rn,*  FROM #Scripts
  )d  join #Scripts sc
  on d.rn=sc.ID
 order by sc.ID

输出

action  ID
Deleted 3
Updated 5
Created 6

关于sql - 在SQL Server 2008中使用分组依据和排序依据检索结果时出错，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/27834125/