时间戳

首页 > 解决方案 > SQL Server GROUP BY 但需要一个详细信息列

sql-server - SQL Server GROUP BY 但需要一个详细信息列

问题描述

我有一个工厂劳动力收集（时间）系统，可以跟踪工人的时间和他们正在从事的工作。在这种特殊情况下，我试图根据每个工作人员在数据表中最近一次滑动的 ON/OFF 类型来确定谁忘记了滑动。考虑三个工人，约翰、马特和肖恩。他们的日子都是从早上 5:30 开始的。以下是一些播放数据：

create table johnmtest (empl_ID varchar(5), ONOFF varchar(2), TSTAMP DATETIME);
insert into jmtest (empl_ID, ONOFF, TSTAMP) values ('JOHNM', 'ON', '2020-01-15 05:31:06');
insert into jmtest (empl_ID, ONOFF, TSTAMP) values ('SEANW', 'ON', '2020-01-15 05:33:17');
insert into jmtest (empl_ID, ONOFF, TSTAMP) values ('MATTD', 'ON', '2020-01-15 05:35:31');
insert into jmtest (empl_ID, ONOFF, TSTAMP) values ('SEANW', 'OF', '2020-01-15 13:07:41');
insert into jmtest (empl_ID, ONOFF, TSTAMP) values ('JOHNM', 'OF', '2020-01-15 14:15:29');
insert into jmtest (empl_ID, ONOFF, TSTAMP) values ('JOHNM', 'ON', '2020-01-15 14:21:11');
insert into jmtest (empl_ID, ONOFF, TSTAMP) values ('MATTD', 'OF', '2020-01-15 15:01:27');
insert into jmtest (empl_ID, ONOFF, TSTAMP) values ('MATTD', 'ON', '2020-01-15 15:15:48');

因此，我们看到 John 在早上 5:31 刷了他的第一个工单，Matt 在 5:35 开始，Sean 在 5:33 开始。这些都是“开”的滑动，伙计们正在工作，时钟正在运行。

所以我试图用他们的 MAX 时间对每个员工进行 GROUP BY。这很容易

select empl_ID, max(TSTAMP) from jmtest GROUP BY empl_ID

但是我需要以某种方式在其中获取ONOFF 列，以便我可以查看员工的最后一次滑动是 ON 还是 OF。我试图做到这一点：

empl_ID     ONOFF    TSTAMP
-------     -----    ------
SEANW        OF      2020-01-15 13:07:41
JOHNM        ON      2020-01-15 14:21:11
MATTD        ON      2020-01-15 15:15:48

事实上，我们打算将这些数据移植到工厂车间的大型电视显示器上，以便主管可以看到谁没有被刷卡。在这种情况下，我希望仅包含 OF 行的附加 WHERE 子句会将列表进一步缩减到仅此：

empl_ID     ONOFF    TSTAMP
-------     -----    ------
SEANW        OF      2020-01-15 13:07:41

然后主管可以去找肖恩对他大喊大叫。虽然只看地板并与数据列表进行比较听起来很容易，但那里有 400 个人。因此，仅 OF 记录显示将非常有帮助。

是否可以进行某种分组，然后加入 ON/OF 列以获取全图？还是两级查询？谢谢，约翰

标签： sql-serverdatetimegroup-bynested

解决方案

这是一个常见问题，以下是它在 TSQL 中的常见实现方式：

with q as
(
    select *, row_number() over (partition by empl_ID order by TSTAMP desc) rn
    from johnmtest
)
select empl_ID, ONOFF, TSTAMP
from q
where rn = 1

输出

empl_ID ONOFF TSTAMP
------- ----- -----------------------
JOHNM   ON    2020-01-15 14:21:11.000
MATTD   ON    2020-01-15 15:15:48.000
SEANW   OF    2020-01-15 13:07:41.000

(3 rows affected)

推荐阅读