SQL从另一个值为最小值的列中获取值

Question

我不是在SQL经验丰富，我试图从另一个值是最小值的行中获得值。我试了好几种方法，但在这个论坛上都找不到答案。

我的SQL:

select  
Name, 
MIN(StartDateAndTime) as StartTime,
MAX(EndDateAndTime) as EndTime
from   tblWorkingTimes 
group by Name, 
convert(date, StartDateAndTime)

所以我有一个表，其中包含司机在白天所做的所有动作(驾驶，休息，加载，…)，我想每天做一个总结。每个动作也给出一个StartPositionText和EndPositionText。(示例" thisstreet666, 9999 City">

上面的SQL工作，但我想添加一个StartPositionText和EndPositionText。所以我需要得到StartPositionText从行哪里w.StartDateAndTime = MIN(w.StartDateAndTime)和EndPositionText从行where w.EndDateAndTime = Max(w.EndDateAndTime)。我尝试了一个SELECT，但我不能在WHERE子句中有MIN(w.s startdateandtime)，我不能让它与HAVING子句一起工作。

编辑:示例数据tblWorkingTimes

EndText2012-09-09 08:28:16StartPlace1">EndPlace1">2012-09-09 09:28:16StartPlace2">EndPlace2">2012-09-09 10:28:16StartPlace3">EndPlace3">2012-09-09 11:28:16StartPlace4">EndPlace4">

可以使用FIRST_VALUE和LAST_VALUE

select  
w.DriverName, 
convert(date, w.StartDateAndTime) as DayPerformed,
MIN(w.StartDateAndTime) as StartTime,
MAX(w.EndDateAndTime) as EndTime
StartPositionText,
EndPositionText
from (
SELECT *,
StartPositionText = FIRST_VALUE(StartPositionText) OVER (PARTITION BY w.DriverName, convert(date, w.StartDateAndTime)
ORDER BY w.StartDateAndTime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
EndPositionText   = LAST_VALUE(EndPositionText) OVER (PARTITION BY w.DriverName, convert(date, w.StartDateAndTime)
ORDER BY w.StartDateAndTime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM webfleet.tblWorkingTimes w
) w
group by w.DriverName, 
convert(date, w.StartDateAndTime),
StartPositionText,
EndPositionText;

与Charlieface的FISRT_VALUE和LAST_VALUE溶液相似，但更直接一些。将开始日期和结束日期的CAST作为日期添加到分区by，使用多个日期进行清理。

DECLARE @t1 TABLE (Driver VARCHAR(50), StartDateTime DATETIME, EndDateTime DATETIME, StartPlace VARCHAR(20), EndPlace VARCHAR(20))
INSERT INTO @t1
VALUES
('Jim','2021-09-09 07:28:16','2021-09-09 08:28:16','    "StartPlace1"','    "EndPlace1"'),
('Jim','2021-09-09 08:28:16','2021-09-09 09:28:16','    "StartPlace2"','    "EndPlace2"'),
('Jim','2021-09-09 09:28:16','2021-09-09 10:28:16','    "StartPlace3"','    "EndPlace3"'),
('Jim','2021-09-09 10:28:16','2021-09-09 11:28:16','    "StartPlace4"','    "EndPlace4"'),
('Jim','2021-09-10 06:28:16','2021-09-10 08:28:16','    "StartPlace1"','    "EndPlace1"'),
('Jim','2021-09-10 08:28:16','2021-09-10 09:28:16','    "StartPlace2"','    "EndPlace2"'),
('Jim','2021-09-10 09:28:16','2021-09-10 10:28:16','    "StartPlace3"','    "EndPlace3"'),
('Jim','2021-09-10 10:28:16','2021-09-10 11:30:16','    "StartPlace4"','    "EndPlace4"')

SELECT DISTINCT
Driver,
CAST(StartDateTime AS DATE) AS 'Date',
FIRST_VALUE(StartDateTime) OVER (PARTITION BY Driver, CAST(StartDateTime AS DATE) ORDER BY StartDateTime  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS StartDateTime,
LAST_VALUE(EndDateTime) OVER (PARTITION BY Driver, CAST(EndDateTime AS DATE) ORDER BY EndDateTime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS EndDateTime,
FIRST_VALUE(StartPlace) OVER (PARTITION BY Driver, CAST(StartDateTime AS DATE) ORDER BY StartDateTime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS StartDateTime,
LAST_VALUE(EndPlace) OVER (PARTITION BY Driver, CAST(EndDateTime AS DATE) ORDER BY EndDateTime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS EndDateTime
FROM @t1

吉姆

Driver	日期	StartDateTime	EndDateTime	StartDateTime	EndDateTime	EndDateTime	tbody> <<tr>	吉姆	2021-09-09	2021-09-09 07:28:16.000	2021-09-09 11:28:16.000	"StartPlace1"	"EndPlace4"
	2021-09-10	2021-09-10 06:28:16.000	2021-09-10 11:30:16.000	"StartPlace1"	"EndPlace4"