如何从SQL Server表中获取一些记录，其中包含最新信息

Question

我有以下SQL Server表：

|infrastructureName|sessionDate            |projectName|idThreat|
|infr1             |2019-06-25 08:49:02.383|projectMain|1
|infr1             |2019-06-25 08:49:02.383|projectMain|2
|infr1             |2019-06-25 08:49:02.383|projectMain|3
|infr1             |2019-06-25 07:49:26.133|projectMain|1
|infr2             |2019-06-25 10:13:30.293|projectMain|1
|infr3             |2019-06-25 12:49:35.383|projectMain|1
|infr3             |2019-06-25 15:49:38.383|projectMain|1

所有四个字段都是主要键。

我想获得下表：

|infrastructureName|sessionDate            |projectName|idThreat|
|infr1             |2019-06-25 08:49:02.383|projectMain|1
|infr1             |2019-06-25 08:49:02.383|projectMain|2
|infr1             |2019-06-25 08:49:02.383|projectMain|3
|infr2             |2019-06-25 10:13:30.293|projectMain|1
|infr3             |2019-06-25 15:49:38.383|projectMain|1

所以，我想获得最新的记录属于项目的所有基础架构的记录。

我该如何在SQL Server中执行此操作？

输出：输出

Answer 1

尝试此

SELECT infrastructureName,sessionDate,projectName
FROM
(
SELECT infrastructureName,sessionDate,projectName, ROW_NUMBER()OVER(PARTITION BY infrastructureName, projectName  ORDER BY sessionDate DESC) AS latest
FROM <YourTable>
)DT WHERE latest = 1

Answer 2

使用 MAX()

SELECT infrastructureName,
projectName, 
idThreat,
MAX(sessionDate) AS sessionDate
FROM your_table
GROUP BY infrastructureName,projectName,idThreat

您的输出不应该那样。我很困惑。这是一种蛮力的方法，可以尝试获取所需的输出而不了解您的数据：

SELECT DISTINCT 
LTRIM(RTRIM(infrastructureName)) AS infrastructureName,
LTRIM(RTRIM(projectName)) AS projectName, 
MAX(sessionDate) AS sessionDate,
LTRIM(RTRIM(idThreat)) AS idThreat
FROM your_table
GROUP BY 
LTRIM(RTRIM(infrastructureName)),
LTRIM(RTRIM(projectName)), 
LTRIM(RTRIM(idThreat)) 

Answer 3

您可以进行十字架应用并获取每个基础架构名称的最大会议日期，然后过滤在等于：

的位置

DECLARE @TestData TABLE
    (
        [infrastructureName] NVARCHAR(100)
      , [sessionDate] DATETIME
      , [projectName] NVARCHAR(100)
      , [idThreat] INT
    );
INSERT INTO @TestData (
                          [infrastructureName]
                        , [sessionDate]
                        , [projectName]
                        , [idThreat]
                      )
VALUES ( 'infr1', '2019-06-25 08:49:02.383', 'projectMain', 1 )
     , ( 'infr1', '2019-06-25 08:49:02.383', 'projectMain', 2 )
     , ( 'infr1', '2019-06-25 08:49:02.383', 'projectMain', 3 )
     , ( 'infr1', '2019-06-25 07:49:26.133', 'projectMain', 1 )
     , ( 'infr2', '2019-06-25 10:13:30.293', 'projectMain', 1 )
     , ( 'infr3', '2019-06-25 12:49:35.383', 'projectMain', 1 )
     , ( 'infr3', '2019-06-25 15:49:38.383', 'projectMain', 1 );

SELECT      [a].*
FROM        @TestData [a]
CROSS APPLY (
                SELECT MAX([aa].[sessionDate]) AS [MostRecentseesionDate]
                FROM   @TestData [aa]
                WHERE  [aa].[infrastructureName] = [a].[infrastructureName]
            ) AS [b]
WHERE       [b].[MostRecentseesionDate] = [a].[sessionDate]
ORDER BY    [a].[infrastructureName];