表:
状态 时间
'0'r(0)p{1} 20130502110210
'-1'r(-30)p{1} 20130502110212
'50'r(90)p{1} 20130502110215
'30'r(130)p{1} 20130502110220
'0'r(0)p{1} 20130502110224说明:
本例为以采集脉冲信号来识别设备的工作状态。
1、状态字段:''里的表示的速度(0为设备停止,其他则为设备开启);{}里的表示设备ID。
2、时间格式:年月日时分秒
求SQL语句:1、根据“状态”字段中的速度,统计一个时间段内该设备的停止和开启时间。
2、查询每天的开停时间,如果连续停止(即连续多条记中的速度为0),则只显示最早的停止时间,如果连续开启(即连续多条记中的速度不为0),则只显示最早的开启时间。
状态 时间
'0'r(0)p{1} 20130502110210
'-1'r(-30)p{1} 20130502110212
'50'r(90)p{1} 20130502110215
'30'r(130)p{1} 20130502110220
'0'r(0)p{1} 20130502110224说明:
本例为以采集脉冲信号来识别设备的工作状态。
1、状态字段:''里的表示的速度(0为设备停止,其他则为设备开启);{}里的表示设备ID。
2、时间格式:年月日时分秒
求SQL语句:1、根据“状态”字段中的速度,统计一个时间段内该设备的停止和开启时间。
2、查询每天的开停时间,如果连续停止(即连续多条记中的速度为0),则只显示最早的停止时间,如果连续开启(即连续多条记中的速度不为0),则只显示最早的开启时间。
用 indexof来获取(你的标识字符串)的位置 然后 获取两者之前的字符串 顺带说下 感觉你这样设计的SQL 不是很好 平白的增加了编程人员的代码量
1.
table oscar_1(status varchar(50),TransDateTime varchar(50))
INSERT INTO oscar_1
VALUES('''0''r(0)p{1}','20130502110210')
INSERT INTO oscar_1
VALUES('''-1''r(-30)p{1}','20130502110212')
INSERT INTO oscar_1
VALUES('''50''r(90)p{1}','20130502110215')
INSERT INTO oscar_1
VALUES('''30''r(130)p{1}','20130502110220')
INSERT INTO oscar_1
VALUES('''0''r(0)p{1}','20130502110224') select case CHARINDEX('''0''',status,1) when '0' then N'停止' else N'开启' end as 'NewStatus',* from oscar_1 order by TransDateTime 2.第二个问题就用笨方法,对临时表做循环,判断每次的开启停止和前一次是否一致,不一致就保存
create table oscar_2(NewStatus Nvarchar(50),status varchar(50),TransDateTime varchar(50))
declare @NewStatus Nvarchar(50)='',@Status varchar(50)='',@TransDateTime varchar(50)=''
declare @PreNewStatus Nvarchar(50)=''
while exists(select top 1 * from #Temp)
begin
select top 1 @NewStatus=NewStatus,@Status=Status,@TransDateTime=TransDateTime from #Temp order by TransDateTime if @NewStatus<>@PreNewStatus
begin
insert into oscar_2 values(@NewStatus,@Status,@TransDateTime)
end delete from #Temp where NewStatus=@NewStatus AND Status=@Status and TransDateTime=@TransDateTime
set @PreNewStatus = @NewStatus
end SELECT * fROM oscar_2
create table tansx
(状态 varchar(20), 时间 varchar(20))insert into tansx
select "'0'r(0)p{1}", '20130502110210' union all
select "'-1'r(-30)p{1}", '20130502110212' union all
select "'50'r(90)p{1}", '20130502110215' union all
select "'30'r(130)p{1}", '20130502110220' union all
select "'0'r(0)p{1}", '20130502110224'
select substring(状态,
charindex('''',状态,1)+1,
charindex('''',状态,charindex('''',状态,1)+1)-charindex('''',状态,1)-1) 'st',
substring(状态,
charindex('{',状态,1)+1,
charindex('}',状态,1)-charindex('{',状态,1)-1) 'de',
cast(left(时间,4)+'-'+substring(时间,5,2)+'-'+substring(时间,7,2)
+' '+substring(时间,9,2)+':'+substring(时间,11,2)+':'+right(时间,2) as datetime) 'dt'
from tansx/*
st de dt
-------------------- -------------------- -----------------------
0 1 2013-05-02 11:02:10.000
-1 1 2013-05-02 11:02:12.000
50 1 2013-05-02 11:02:15.000
30 1 2013-05-02 11:02:20.000
0 1 2013-05-02 11:02:24.000(5 row(s) affected)
*/
with t as
(select substring(状态,
charindex('''',状态,1)+1,
charindex('''',状态,charindex('''',状态,1)+1)-charindex('''',状态,1)-1) 'st',
substring(状态,
charindex('{',状态,1)+1,
charindex('}',状态,1)-charindex('{',状态,1)-1) 'de',
cast(left(时间,4)+'-'+substring(时间,5,2)+'-'+substring(时间,7,2)
+' '+substring(时间,9,2)+':'+substring(时间,11,2)+':'+right(时间,2) as datetime) 'dt'
from tansx
)
select de,
min(case when st='0' then dt else null end) '停止时间',
min(case when st!='0' then dt else null end) '开启时间'
from t
group by de
/*
de 停止时间 开启时间
-------------------- ----------------------- -----------------------
1 2013-05-02 11:02:10.000 2013-05-02 11:02:12.000(1 row(s) affected)
*/
状态应该拆成三个字段,时间换成datetime类型
非常感谢!
1、第二个结果正是我要的,第一个结果需要显示分别开了多长时间(即开了多少个小时数)和停了多长时间(即停了多少个小时)。
2、我用的是SQL 2000,请教SQL 2000的语句。
再次感谢!