MIN(列)和MAX(列)对应的提取位置列

Question

Select City, Name, MarkDate,Location MIN(InTime) as InTime
,MAX(OutTime)as OutTime,SUM(Distance)  as Distance,SUM(Duration) as
Duration from tblAttendanceTracker JOIN tblEmployee ON
tblAttendanceTracker.EId= tblEmployee.EId Group By MarkDate,City ,Name
Order By MarkDate ,City,Name asc

由于Location是单列，我如何获取MIN(InTime)对应的Location列和MAX(OuTTime)对应的Location

Answer 1

通常情况下，您提供的SQL根本无法工作，并会出现错误。我收到了你的问题，因为你想根据进出日期找到每个员工和城市的位置：

WITH summary
AS (SELECT Eid,
City,
MarkDate,
MIN(InTime) AS InTime,
MAX(OutTime) AS OutTime,
SUM(Distance) AS Distance,
SUM(Duration) AS Duration
FROM tblAttendanceTracker
GROUP BY Eid,
MarkDate,
City)
SELECT s.City,
e.Name,
s.MarkDate,
t.Location,
s.InTime,
s.OutTime,
s.Distance,
s.Duration
FROM summary s
INNER JOIN tblAttendanceTracker t
ON s.Eid = t.Eid
AND s.City = t.City
AND s.Markdate = t.Markdate
AND
(
s.InTime = t.InTime
OR s.OutTime = t.OutTime
)
INNER JOIN tblEmployee e
ON s.EId = e.EId
ORDER BY s.MarkDate,
s.City,
e.Name ASC;