22,209
社区成员
发帖
与我相关
我的任务
分享
货柜 时间 司机
ccc 2013-06-01 13:30:31 ABC
yyy 2013-06-01 13:37:30 BMW
yyy 2013-06-01 13:40:31 BMW
ccc 2013-06-01 13:41:31 ABC
ccc 2013-06-01 13:45:20 ABC
yyy 2013-06-01 14:30:31 BMW
yyy 2013-06-02 11:40:31 BMW
ccc 2013-06-02 12:15:21 000
ccc 2013-06-02 13:10:30 000
ccc 2013-06-02 13:20:31 000
yyy 2013-06-02 13:40:31 BMW
ccc 2013-06-03 11:30:31 ABC
ccc 2013-06-03 12:40:31 ABC
ccc 2013-06-03 13:50:31 ABC
ccc 2013-06-03 14:55:31 ABC
DECLARE @container nvarchar(40)
DECLARE @starttime datetime
DECLARE @endtime datetime
DECLARE @curtime datetime
DECLARE @driver nvarchar(40)
DECLARE @curdriver nvarchar(40)
declare @curstate int
set @curstate=0
declare contcursor cursor
for select distinct(货柜)
FROM GROUBYTIME
open contcursor
fetch next from contcursor into @container
while @@FETCH_STATUS=0
begin
declare timecursor cursor
for select 时间,司机
FROM GROUBYTIME
where 货柜=@container
open timecursor
fetch next from timecursor into @curtime,@curdriver
WHILE @@FETCH_STATUS = 0
begin
if @curstate=0
begin --初始化
set @driver=@curdriver
set @starttime=@curtime
set @endtime=@starttime
set @curstate=1
end
if @driver<>@curdriver
begin
--换司机,获取结束时间,输出,更换开始时间
select @container,@starttime,@endtime,@driver
set @driver=@curdriver
set @starttime=@curtime
end
else
begin
--未换司机,更换结束时间
set @endtime=@curtime
end
fetch next from timecursor into @curtime,@curdriver
end
select @container,@starttime,@endtime,@driver
set @curstate=0--下一个货柜初始化
close timecursor
deallocate timecursor
fetch next from contcursor into @container
end
close contcursor
deallocate contcursor
CREATE table GROUBYTIME(
Trailer NVARCHAR(40),
CheckedOn DATETIME,
Driver NVARCHAR(30))
INSERT INTO GROUBYTIME
SELECT'ccc','2013-06-01 13:30:31','ABC' UNION ALL
SELECT 'yyy','2013-06-01 13:37:30','BMW' UNION ALL
SELECT 'yyy','2013-06-01 13:40:31','BMW' UNION ALL
SELECT 'ccc','2013-06-01 13:41:31','ABC' UNION ALL
SELECT 'ccc','2013-06-01 13:45:20','ABC' UNION ALL
SELECT 'yyy','2013-06-01 14:30:31','BMW' UNION ALL
SELECT 'yyy','2013-06-02 11:40:31','BMW' UNION ALL
SELECT 'ccc','2013-06-02 12:15:21','000' UNION ALL
SELECT 'ccc','2013-06-02 13:10:30','000' UNION ALL
SELECT 'ccc','2013-06-02 13:20:31','000' UNION ALL
SELECT 'yyy','2013-06-02 13:40:31','BMW' UNION ALL
SELECT 'ccc','2013-06-03 11:30:31','ABC' UNION ALL
SELECT 'ccc','2013-06-03 12:40:31','ABC' UNION ALL
SELECT 'ccc','2013-06-03 13:50:31','ABC' UNION ALL
SELECT 'ccc','2013-06-03 14:55:31','ABC'
SELECT t1.Trailer, t1.CheckedOn AS StartTime, t2.CheckedOn AS EndTime
FROM
(SELECT Trailer,CheckedOn,Driver, ROW_NUMBER() OVER(PARTITION BY Trailer ORDER BY CheckedOn) AS Row FROM GROUBYTIME) t1
LEFT JOIN
(SELECT Trailer, CheckedOn, ROW_NUMBER() OVER(PARTITION BY Trailer ORDER BY CheckedOn) AS Row FROM GROUBYTIME) t2
ON t1.Trailer = t2.Trailer AND t1.Row = t2.Row -1
WHERE t1.Row % 2 = 1
ORDER BY t1.Trailer, t1.CheckedOn
DECLARE @Begin DATETIME, @END DATETIME
SET @Begin = '06/01/2013'
SET @End = '06/02/2013'
SELECT 货柜,MIN(时间) as 开始时间,MAX(时间) as 结束时间,司机
FROM GROUBYTIME
WHERE CONVERT(VARCHAR(10), 时间, 102) BETWEEN CONVERT(VARCHAR(10),@Begin, 102) AND CONVERT(VARCHAR(10),@End,102)
GROUP BY 货柜,司机
-- 如求每天一天之内的搭配
SELECT 货柜,MIN(时间) as 开始时间,MAX(时间) as 结束时间,司机
FROM GROUBYTIME
GROUP BY CONVERT(VARCHAR(10), 时间, 102), 货柜,司机
/*create table GROUBYTIME(
货柜 NVARCHAR(40),
时间 DATETIME,
司机 NVARCHAR(30))
INSERT INTO GROUBYTIME
SELECT'ccc','2013-06-01 13:30:31','ABC' UNION ALL
SELECT 'yyy','2013-06-01 13:37:30','BMW' UNION ALL
SELECT 'yyy','2013-06-01 13:40:31','BMW' UNION ALL
SELECT 'ccc','2013-06-01 13:41:31','ABC' UNION ALL
SELECT 'ccc','2013-06-01 13:45:20','ABC' UNION ALL
SELECT 'yyy','2013-06-01 14:30:31','BMW' UNION ALL
SELECT 'yyy','2013-06-02 11:40:31','BMW' UNION ALL
SELECT 'ccc','2013-06-02 12:15:21','000' UNION ALL
SELECT 'ccc','2013-06-02 13:10:30','000' UNION ALL
SELECT 'ccc','2013-06-02 13:20:31','000' UNION ALL
SELECT 'yyy','2013-06-02 13:40:31','BMW' UNION ALL
SELECT 'ccc','2013-06-03 11:30:31','ABC' UNION ALL
SELECT 'ccc','2013-06-03 12:40:31','ABC' UNION ALL
SELECT 'ccc','2013-06-03 13:50:31','ABC' UNION ALL
SELECT 'ccc','2013-06-03 14:55:31','ABC'*/
SELECT 货柜,时间,司机 FROM GROUBYTIME
SELECT 货柜,MIN(时间) as 开始时间,MAX(时间) as 结束时间,司机 FROM GROUBYTIME GROUP BY 货柜,司机,DAY(时间)
ccc 2013-06-02 12:15:21.000 2013-06-02 13:20:31.000 000
ccc 2013-06-01 13:30:31.000 2013-06-01 13:45:20.000 ABC
ccc 2013-06-03 11:30:31.000 2013-06-03 14:55:31.000 ABC
yyy 2013-06-01 13:37:30.000 2013-06-01 14:30:31.000 BMW
yyy 2013-06-02 11:40:31.000 2013-06-02 13:40:31.000 BMW
2013-06-01 14:30:31.000 -2013-06-02 11:40:31.000 BMW还开?