CSDN CodeDECLARE @T TABLE (ID INT,BeginTime DATETIME,EndTime DATETIME,DoorNo INT)
INSERT INTO @T
SELECT 5,'12:00','18:00',1 UNION ALL
SELECT 2,'12:00','18:00',2 UNION ALL
SELECT 6,'18:05','00:00',1 UNION ALL
SELECT 3,'18:10','00:03',2 UNION ALL
SELECT 7,'00:02','06:03',1 UNION ALL
SELECT 1,'06:10','11:55',1
select * from @T
--这是个值班表，我想查ID号为6的员工的上班记录和他的上一班和下一班【在1号门值班】的员工的ID
--需要查询的结果：
--员工ID，上班时间，下班时间，上班地点，上一班员工编号，下一班员工编号
-- 6 18:05 24:00 1 5 7
1.
select ID,BeginTime,EndTime,DoorNo
,PreID=(select top 1 ID from @T where DoorNo=t.DoorNo and EndTime<t.BeginTime order by EndTime desc)
,NextID=(select top 1 ID from @T where DoorNo=t.DoorNo and BeginTime>t.EndTime order by BeginTime)
from @T t
where ID=6
2.
SELECT A.*,C.ID AS 上一班员工编号,B.ID AS 下一班员工编号
FROM @T AS A
CROSS APPLY(SELECT TOP(1) ID FROM @T
WHERE BeginTime>A.EndTime
AND DoorNo=A.DoorNo
ORDER BY BeginTime) AS B
CROSS APPLY(SELECT TOP(1) ID FROM @T
WHERE EndTime<A.BeginTime
AND DoorNo=A.DoorNo
ORDER BY EndTime DESC) AS C
WHERE A.ID=6
