tsql组按最小和最大时间为同一日期

Question

我有一个数据，其中每个人可以有事件的时间进入和事件超时数据，可以是多次在同一日期记录迈克在下面的代码。

DECLARE @vtable TABLE 
(
    Id Int NOT NULL, 
    Name VARCHAR(MAX) NOT NULL,
    EventDate date NOT NULL, 
    EventTime time NOT NULL
);

INSERT INTO @vtable (Id, Name, EventDate, EventTime)
VALUES (1, 'Mike', '2022-10-10', '08:00'),
       (2, 'Mike', '2022-10-10', '11:00'),
       (3, 'Mike', '2022-10-10', '12:00'),
       (4, 'Mike', '2022-10-10', '18:00'),
       (5, 'Jen', '2022-10-10', '09:00'),
       (6, 'Jen', '2022-10-10', '12:00'),
       (7, 'Jen', '2022-10-11', '14:00'),
       (8, 'Jen', '2022-10-11', '18:00')

我对以下查询进行了尝试，但没有得到所需的结果：

SELECT 
    Name, EventDate, 
    MIN(EventTime) AS In, MAX(EventTime) AS Out 
FROM
    @vtable 
GROUP BY
    Name, EventDate

我想得到这样的结果：

​

​

Answer 1

一个简化的解决方案是使用适当的分组：

SELECT Name, EventDate, MIN(EventTime) AS [In], MAX(EventTime) AS [Out]
FROM (
   SELECT *, ROW_NUMBER() OVER (PARTITION BY Name, EventDate ORDER BY Id) AS Rn
   FROM @vtable
) t
GROUP BY Name, EventDate, (Rn - 1) / 2
ORDER BY Name, EventDate