时间戳

首页 > 解决方案 > 关于从两列中获取具有最大日期的记录的 SQL 查询

sql - 关于从两列中获取具有最大日期的记录的 SQL 查询

问题描述

我有一个这样的 SQL Server 表：

EMPs

Id  Name    DateOn              DateOff     
-------------------------------------------------
1   EMP1    2020-9-4 12:00 AM   NULL
2   EMP1    2020-9-4 12:00 AM   2020-9-4 10:00 PM
3   EMP2    2020-7-4 12:00 AM   NULL
4   EMP4    2020-7-4 12:00 AM   2020-9-4 10:00 PM

另一个查询示例

DECLARE @EMP TABLE
(
    Id INT,
    NAME VARCHAR(200),
    AlarmOnTimeStamp DATETIMEOFFSET,
    AlarmOffTimeStamp DATETIMEOFFSET NULL
);


INSERT INTO @EMP VALUES(1,'Test1','2020-04-09 01:56:29.507',NULL)
INSERT INTO @EMP VALUES(2,'Test1','2020-04-09 01:56:29.507','2020-04-09 03:56:29.507')
INSERT INTO @EMP VALUES(3,'Test2','2020-04-09 01:56:29.507','2020-04-09 03:56:29.507')


select * from (
    select *,
           row_number() over (order by a.AlarmOffTimestamp  desc) rn
    from @EMP a
) a where rn = 1

在此处输入图像描述

Id列是唯一的。

我想要一个 SQL 查询，要从上面的示例中获取单个记录，我应该得到 Id => 2,3 和第 4 条记录；我很困惑如何将两列 Max dates 与 group by 语句一起使用。

标签： sqlsql-servertsqlgreatest-n-per-group

解决方案

您可以使用子查询进行过滤：

select e.*
from emps e
where e.id = (
    select top (1) id 
    from emps e1
    where e1.name = e.name 
    order by e1.dateon desc, e1.dateoff desc
)

目前还不是很清楚你想要什么排序标准；这为您提供了最新的记录dateon；如果有关系，dateoff则用于打破它们。您可能希望order by根据您的确切要求调整子查询的子句。

即使针对大型数据集，此解决方案通常也非常有效。出于性能考虑，您需要在(name, dateon, dateoff). 您也可以尝试添加id索引中的最后一个位置，例如(name, dateon, dateoff, id)：这使得索引覆盖，这意味着数据库可以通过仅查看索引来执行子查询。

DB Fiddle 上的演示

样本数据：

身份证 | 姓名 | AlarmOnTimeStamp | AlarmOffTimeStamp          
-: | :---- | :---------------------------- | :----------------------------
 1 | 测试1 | 2020-04-09 01:56:29.5070000 | 空值                       
 2 | 测试1 | 2020-04-09 01:56:29.5070000 | 2020-04-09 03:56:29.5070000
 3 | 测试2 | 2020-04-09 01:56:29.5070000 | 2020-04-09 03:56:29.5070000

结果：

身份证 | 姓名 | AlarmOnTimeStamp | AlarmOffTimeStamp          
-: | :---- | :---------------------------- | :----------------------------
 2 | 测试1 | 2020-04-09 01:56:29.5070000 | 2020-04-09 03:56:29.5070000
 3 | 测试2 | 2020-04-09 01:56:29.5070000 | 2020-04-09 03:56:29.5070000

推荐阅读