SQL统计汇总的问题，请高手指教
数据库表结构如下：
煤矿名称（varchar(50)），传感器编号（varchar(10）），传感器名称（varchar(50）），传感器类型（varchar(10）），状态（int），当前值（float）,当前时间（datetime）
模拟数据为：
A矿 c001 一层传感器 风门 0 0.0 2007-1-31 00:00:00
A矿 c001 一层传感器 风门 1 0.0 2007-1-31 00:00:20
A矿 c001 一层传感器 风门 0 0.0 2007-1-31 18:40:15
A矿 c001 一层传感器 风门 1 0.0 2007-1-31 18:45:20
A矿 c001 一层传感器 风门 128 0.0 2007-1-31 20:30:13
A矿 c001 一层传感器 风门 1 0.0 2007-1-31 21:30:25
A矿 c001 一层传感器 风门 128 0.0 2007-1-31 22:20:10
A矿 c001 一层传感器 风门 0 0.0 2007-1-31 22:50:44
A矿 c001 一层传感器 风门 1 0.0 2007-1-31 23:00:44
A矿 c001 一层传感器 风门 0 0.0 2007-1-31 23:50:44
A矿 c002 二层传感器 风机 0 0.0 2007-1-31 00:00:00
A矿 c002 二层传感器 风机 1 0.0 2007-1-31 00:10:10
B矿 d001 总回风 风门 0 0.0 2007-1-31 00:00:00
B矿 d001 总回风 风门 1 0.0 2007-1-31 00:20:10
B矿 d001 总回风 风门 0 0.0 2007-1-31 23:20:44
B矿 d001 总回风 风门 1 0.0 2007-1-31 23:40:44
状态有0，1，128等 0表示打开，1表示关闭，128表示故障
希望分组统计得到信息如下：
矿名 编号 名称 类型 累计开次数 累计开时间 累计关次数 累计关时间
A矿 c001 一层传感器 风门 4 00:19:51 6 23:40:09
A矿 c002 二层传感器 风机 1 00:10:10 1 23:49:50
B矿 d001 总回风 风门 2 00:40:10 2 23:19:50
有一次0记一次开次数，不为0时都记为关次数
累计时间为状态从0到1改变为开时间，遇128默认为关状态，遇到128时数据显示不规则，是否能在SQL统计中实现希望得到的分组统计效果，如果不行，是否可以放到程序中对符合条件的记录集合再次拆分统计，得到欲实现效果，有好的实现的意见或办法也欢迎大家指教，谢谢了
[解决办法]
declare @t1 table (Mname varchar(10),SCode varchar(10),SName varchar(20),SType varchar(10),Status int,Value float,Dates datetime)
insert into @t1 select 'A矿 ', 'c001 ', '一层传感器 ', '风门 ', 0 ,0.0, '2007-1-31 00:00:00 '
insert into @t1 select 'A矿 ', 'c001 ', '一层传感器 ', '风门 ', 1 ,0.0, '2007-1-31 00:00:20 '
insert into @t1 select 'A矿 ', 'c001 ', '一层传感器 ', '风门 ', 0 ,0.0, '2007-1-31 18:40:15 '
insert into @t1 select 'A矿 ', 'c001 ', '一层传感器 ', '风门 ', 1 ,0.0, '2007-1-31 18:45:20 '
insert into @t1 select 'A矿 ', 'c001 ', '一层传感器 ', '风门 ',128,0.0, '2007-1-31 20:30:13 '
insert into @t1 select 'A矿 ', 'c001 ', '一层传感器 ', '风门 ', 1 ,0.0, '2007-1-31 21:30:25 '
insert into @t1 select 'A矿 ', 'c001 ', '一层传感器 ', '风门 ',128,0.0, '2007-1-31 22:20:10 '
insert into @t1 select 'A矿 ', 'c001 ', '一层传感器 ', '风门 ', 0 ,0.0, '2007-1-31 22:50:44 '
insert into @t1 select 'A矿 ', 'c001 ', '一层传感器 ', '风门 ', 1 ,0.0, '2007-1-31 23:00:44 '
insert into @t1 select 'A矿 ', 'c001 ', '一层传感器 ', '风门 ', 0 ,0.0, '2007-1-31 23:50:44 '
insert into @t1 select 'A矿 ', 'c002 ', '二层传感器 ', '风机 ', 0 ,0.0, '2007-1-31 00:00:00 '
insert into @t1 select 'A矿 ', 'c002 ', '二层传感器 ', '风机 ', 1 ,0.0, '2007-1-31 00:10:10 '
insert into @t1 select 'B矿 ', 'd001 ', '总回风 ', '风门 ', 0 ,0.0, '2007-1-31 00:00:00 '
insert into @t1 select 'B矿 ', 'd001 ', '总回风 ', '风门 ', 1 ,0.0, '2007-1-31 00:20:10 '
insert into @t1 select 'B矿 ', 'd001 ', '总回风 ', '风门 ', 0 ,0.0, '2007-1-31 23:20:44 '
insert into @t1 select 'B矿 ', 'd001 ', '总回风 ', '风门 ', 1 ,0.0, '2007-1-31 23:40:44 '
select
t.MName as 矿名,
t.SCode as 编号,
t.SName as 名称,
t.SType as 类型,
sum(case t.Status when 0 then 1 else 0 end) as 累计开次数,
convert(char(8),dateadd(ss,sum(t.Times),0),108) as 累计开时间,
sum(case t.Status when 0 then 0 else 1 end) as 累计关次数,
convert(char(8),dateadd(ss,(24*60*60-sum(t.Times)),0),108) as 累计关时间
from
(select
a.*,
(case a.Status
when 0
then datediff(ss,a.Dates,isnull(
(select
top 1 Dates
from
@t1
where
Mname=a.Mname and SCode=a.SCode and Status!=0 and Dates> a.Dates
order by
Dates),convert(char(10),dateadd(d,1,a.Dates),120))) else 0 end) as Times
from
@t1 a ) t
group by
t.MName,t.SCode,t.SName,t.SType
/*
矿名 编号 名称 类型 累计开次数 累计开时间 累计关次数 累计关时间
---------- ---------- ------------- ---------- ----------- ---------- ----------- ----------
A矿 c001 一层传感器 风门 4 00:24:41 6 23:35:19
A矿 c002 二层传感器 风机 1 00:10:10 1 23:49:50
B矿 d001 总回风 风门 2 00:40:10 2 23:19:50
*/
[解决办法]
libin_ftsafe(子陌红尘：当libin告别ftsafe) 的算法不忍心改，呵呵
declare @t1 table (Mname varchar(10),SCode varchar(10),SName varchar(20),SType varchar(10),Status int,Value float,Dates datetime)
insert into @t1 select 'A矿 ', 'c001 ', '一层传感器 ', '风门 ', 0 ,0.0, '2007-1-30 00:00:00 '
insert into @t1 select 'A矿 ', 'c001 ', '一层传感器 ', '风门 ', 1 ,0.0, '2007-1-30 00:00:20 '
insert into @t1 select 'A矿 ', 'c001 ', '一层传感器 ', '风门 ', 0 ,0.0, '2007-1-30 18:40:15 '
insert into @t1 select 'A矿 ', 'c001 ', '一层传感器 ', '风门 ', 1 ,0.0, '2007-1-30 18:45:20 '
insert into @t1 select 'A矿 ', 'c001 ', '一层传感器 ', '风门 ',128,0.0, '2007-1-30 20:30:13 '
insert into @t1 select 'A矿 ', 'c001 ', '一层传感器 ', '风门 ', 1 ,0.0, '2007-1-30 21:30:25 '
insert into @t1 select 'A矿 ', 'c001 ', '一层传感器 ', '风门 ',128,0.0, '2007-1-30 22:20:10 '
insert into @t1 select 'A矿 ', 'c001 ', '一层传感器 ', '风门 ', 0 ,0.0, '2007-1-30 22:50:44 '
insert into @t1 select 'A矿 ', 'c001 ', '一层传感器 ', '风门 ', 1 ,0.0, '2007-1-30 23:00:44 '
insert into @t1 select 'A矿 ', 'c001 ', '一层传感器 ', '风门 ', 0 ,0.0, '2007-1-30 23:50:44 '
insert into @t1 select 'A矿 ', 'c002 ', '二层传感器 ', '风机 ', 0 ,0.0, '2007-1-01 00:00:00 '
insert into @t1 select 'A矿 ', 'c002 ', '二层传感器 ', '风机 ', 1 ,0.0, '2007-1-30 00:10:10 '
insert into @t1 select 'B矿 ', 'd001 ', '总回风 ', '风门 ', 0 ,0.0, '2007-1-30 00:00:00 '
insert into @t1 select 'B矿 ', 'd001 ', '总回风 ', '风门 ', 1 ,0.0, '2007-1-30 00:20:10 '
insert into @t1 select 'B矿 ', 'd001 ', '总回风 ', '风门 ', 0 ,0.0, '2007-1-30 23:20:44 '
insert into @t1 select 'B矿 ', 'd001 ', '总回风 ', '风门 ', 1 ,0.0, '2007-1-30 23:40:44 '
declare @FirstDay datetime
declare @NextMonth datetime
declare @Days int
select @FirstDay=convert(varchar(8),min(dates),120)+ '01 '
from @t1
set @NextMonth=dateadd(month,1,@FirstDay)
set @Days=datediff(day,@FirstDay,@NextMonth)
print @NextMonth
print @Days
select
t.MName as 矿名,
t.SCode as 编号,
t.SName as 名称,
t.SType as 类型,
sum(case t.Status when 0 then 1 else 0 end) as 累计开次数,
case when sum(t.Times) <24*60*60 then convert(varchar(8),dateadd(ss,sum(t.Times),0),108)
else right(convert(char(19),dateadd(ss,sum(t.Times),-1),120),11) end as 累计开时间,
sum(case t.Status when 0 then 0 else 1 end) as 累计关次数,
right(convert(char(19),dateadd(ss,(@Days*24*60*60-sum(t.Times)),-1),120),11) as 累计关时间
from
(select
a.*,
(case a.Status
when 0
then datediff(ss,a.Dates,isnull(
(select
top 1 Dates
from
@t1
where
Mname=a.Mname and SCode=a.SCode and Status!=0 and Dates> a.Dates
order by
Dates),@NextMonth)) else 0 end) as Times
from
@t1 a ) t
group by
t.MName,t.SCode,t.SName,t.SType
--结果
矿名 编号 名称 类型 累计开次数 累计开时间 累计关次数 累计关时间
---------- ---------- ------------- ---------- ----------- ------------- ----------- -------------
A矿 c001 一层传感器 风门 4 01 00:24:41 6 29 23:35:19
A矿 c002 二层传感器 风机 1 29 00:10:10 1 01 23:49:50
B矿 d001 总回风 风门 2 00:40:10 2 30 23:19:50
