说明计算机 :B01 --- ID=1
B02 --- ID=2
B03 --- ID=3状态 : 工作 --- ID=1
损坏 --- ID=2
暂停 --- ID=3日志 :
编号 计算机 状态 时间
ID CID State E_Time
1 1 1 2006-1-1 7:0:0
2 2 1 2006-1-1 7:0:1
3 3 1 2006-1-1 7:0:2
4 2 2 2006-1-20 19:25:30
5 3 3 2006-1-28 19:25:30
6 2 1 2006-1-29 10:20:30
7 2 3 2006-1-29 11:20:0
8 2 1 2006-2-3 8:20:0
9 3 1 2006-2-3 8:26:0
以流水帐形式记录计算机状态的变化请问怎么实现以下查询1 计算在一个时间段内 计算机 (如 B02)的使用时间
2 计算在一个时间点 计算机 (如 B02)的使用状态(工作,损坏,暂停)谢谢
B02 --- ID=2
B03 --- ID=3状态 : 工作 --- ID=1
损坏 --- ID=2
暂停 --- ID=3日志 :
编号 计算机 状态 时间
ID CID State E_Time
1 1 1 2006-1-1 7:0:0
2 2 1 2006-1-1 7:0:1
3 3 1 2006-1-1 7:0:2
4 2 2 2006-1-20 19:25:30
5 3 3 2006-1-28 19:25:30
6 2 1 2006-1-29 10:20:30
7 2 3 2006-1-29 11:20:0
8 2 1 2006-2-3 8:20:0
9 3 1 2006-2-3 8:26:0
以流水帐形式记录计算机状态的变化请问怎么实现以下查询1 计算在一个时间段内 计算机 (如 B02)的使用时间
2 计算在一个时间点 计算机 (如 B02)的使用状态(工作,损坏,暂停)谢谢
select top 1 state from testtable where cid='B02' and e_time<='2005-11-11 11:11:11.000' order by e_time desc;
create table [dbo].[#mytable] (
[iid] [char] (1) collate chinese_prc_ci_as not null ,
[cid] [char] (1) collate chinese_prc_ci_as not null ,
[state] [char] (1) collate chinese_prc_ci_as null ,
[dt] [datetime] null
) on [primary]
go
--写入测试数据
insert into #mytable
select 1, 1, 1, '2006-1-1 7:0:0'
union
select 2, 2, 1, '2006-1-1 7:0:1'
union
select 3, 3, 1, '2006-1-1 7:0:2'
union
select 4, 2, 2, '2006-1-20 19:25:30'
union
select 5, 3, 3, '2006-1-28 19:25:30'
union
select 6, 2, 1, '2006-1-29 10:20:30'
union
select 7, 2, 3, '2006-1-29 11:20:0'
union
select 8, 2, 1, '2006-2-3 8:20:0'
union
select 9, 3, 1, '2006-2-3 8:26:0'
--完成计算时间
declare @cid as int
declare @beg as datetime
declare @end as datetime
declare @t as datetime
declare @t_out as datetime
declare @worktime as int --工作时间
declare @startstate as int
declare @result as varchar(200)
--根据需要,替换以下值
set @cid=2
set @beg='2006-1-1 7:03'
set @end='2006-02-03 8:10:00'
set @worktime=0
--起始时是正常工作(第一个记录的state不为1)
select top 1 @startstate=state from #mytable where cid=@cid and dt between @beg and @end order by dt
if @startstate<>1
begin
select top 1 @worktime=isnull(datediff(n,@beg,dt),0) from #mytable where cid=@cid and dt between @beg and @end order by dt
end
--打开游标
declare my_cursor cursor for
select dt from #mytable where cid=@cid and dt between @beg and @end and state=1--状态为1表示正常工作
open my_cursor
fetch next from my_cursor into @t
while @@fetch_status = 0
begin
--找寻本段时间内,非正常工作的状态,即上次正常工作的结束时间
select top 1 @t_out=dt from #mytable where cid=@cid and dt between @t and @end and state<>1
if @t_out is null --已到结束
set @worktime=@worktime+datediff(n,@t,@end)
else
set @worktime=@worktime+datediff(n,@t,@t_out)
fetch next from my_cursor into @t
end
--销毁游标
close my_cursor
deallocate my_cursor
set @result=cast(@worktime/60/24 as varchar) + '天' + cast((@worktime - @worktime/60/24 * 60 * 24)/60 as varchar) + '小时' +
cast(@worktime- @worktime/60/24 * 60 * 24-(@worktime - @worktime/60/24 * 60 * 24)/60 * 60 as varchar) + '分钟'
select @result as resultdrop table #mytable
create table [dbo].[#mytable] (
[iid] [char] (1) collate chinese_prc_ci_as not null ,
[cid] [char] (1) collate chinese_prc_ci_as not null ,
[state] [char] (1) collate chinese_prc_ci_as null ,
[dt] [datetime] null
) on [primary]
go
--写入测试数据
insert into #mytable
select 1, 1, 1, '2006-1-1 7:0:0'
union
select 2, 2, 1, '2006-1-1 7:0:1'
union
select 3, 3, 1, '2006-1-1 7:0:2'
union
select 4, 2, 2, '2006-1-20 19:25:30'
union
select 5, 3, 3, '2006-1-28 19:25:30'
union
select 6, 2, 1, '2006-1-29 10:20:30'
union
select 7, 2, 3, '2006-1-29 11:20:0'
union
select 8, 2, 1, '2006-2-3 8:20:0'
union
select 9, 3, 1, '2006-2-3 8:26:0'
--完成计算时间
declare @cid as int
declare @beg as datetime
declare @end as datetime
declare @t as datetime
declare @t_out as datetime
declare @worktime as int--工作时间
declare @startstate as int
declare @result as varchar(200)
--根据需要,替换以下值
set @cid=1
set @beg='2006-01-18 0:00:00.000'
set @end='2006-02-01 0:00:00.000'
set @worktime=0--起始时是正常工作(第一个记录的state不为1)
select top 1 @startstate=state from #mytable where cid=@cid and dt between @beg and @end order by dt
if @startstate<>1
select top 1 @worktime=isnull(datediff(n,@beg,dt),0) from #mytable where cid=@cid and dt between @beg and @end order by dt
if @startstate is null --当前时段内没有记录,以最近一次的标志得到工作时间,要么为0,要么为当前时段所有时间
begin
select top 1 @startstate=state from #mytable where cid=@cid and dt < @beg order by dt DESC
if @startstate=1
select @worktime=isnull(datediff(n,@beg,@end),0)
end--打开游标
declare my_cursor cursor for
select dt from #mytable where cid=@cid and dt between @beg and @end and state=1--状态为1表示正常工作
open my_cursor
fetch next from my_cursor into @twhile @@fetch_status = 0
begin
--找寻本段时间内,非正常工作的状态,即上次正常工作的结束时间
select top 1 @t_out=dt from #mytable where cid=@cid and dt between @t and @end and state<>1
if @t_out is null --已到结束
set @worktime=@worktime+datediff(n,@t,@end)
else
set @worktime=@worktime+datediff(n,@t,@t_out)
fetch next from my_cursor into @t
end
--销毁游标
close my_cursor
deallocate my_cursor
set @result=cast(@worktime/60/24 as varchar) + '天' + cast((@worktime - @worktime/60/24 * 60 * 24)/60 as varchar) + '小时' +
cast(@worktime- @worktime/60/24 * 60 * 24-(@worktime - @worktime/60/24 * 60 * 24)/60 * 60 as varchar) + '分钟'select * from #mytable
select @result as resultdrop table #mytable