解决方案 »
- 两个数据库之间的内容替换(马上结贴,在线等)
- 数据访问的实质
- 简单问题
- 急求,有关distinct的问题
- 向邹老师请教一个两台sqlserver 2005数据库服务器数据同步的问题,谢谢
- 如何获取另一个存储过程的output值?
- 我提了二個問題,有三四天了,仍未解決,請問我該如何結貼
- 请大家访问我的个人站点--Delphi桃花岛,绝对值得去一趟,有数据库辅助设计Case工具(支持Sql Server7/2k)以及数据库资料
- 请问一下在子查询中有in 关键字的存储过程怎么写。谢谢
- 请提供些sql server的新闻组.
- 毕业设计答辩数据库出现问题,加急啊
- 数据库服务器、网站服务器分离,如何高效连接访问
/*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 BMW2013-06-01 14:30:31.000 -2013-06-02 11:40:31.000 BMW还开?
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), 货柜,司机
ccc 2013-06-02 11:01:31.000 2013-06-02 15:00:20.000 DDD
ccc 2013-06-03 15:00:31.000 2013-06-03 17:30:31.000 ABC
yyy 2013-06-01 13:37:30.000 2013-06-02 13:40:31.000 BMW
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 @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 GROUBYTIMEopen contcursor
fetch next from contcursor into @containerwhile @@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