日期:2014-05-17 浏览次数:20586 次
if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (meter_code nvarchar(28),read_date datetime,read_data int)
insert into [TB]
select 'meter_00001314','2013-04-20 00:00:00.000',0 union all
select 'meter_00001314','2013-04-21 00:00:00.000',0 union all
select 'meter_00001314','2013-04-22 00:00:00.000',0 union all
select 'meter_00001314','2013-04-23 00:00:00.000',0 union all
select 'meter_00001314','2013-04-24 00:00:00.000',0 union all
select 'meter_00001314','2013-04-25 00:00:00.000',13714 union all
select 'meter_00001314','2013-04-26 00:00:00.000',0 union all
select 'meter_00001314','2013-04-27 00:00:00.000',13773 union all
select 'meter_00001314','2013-04-28 00:00:00.000',14717 union all
select 'meter_00001314','2013-04-29 00:00:00.000',0 union all
select 'meter_00001315','2013-04-20 00:00:00.000',0 union all
select 'meter_00001315','2013-04-21 00:00:00.000',0 union all
select 'meter_00001315','2013-04-22 00:00:00.000',0 union all
select 'meter_00001315','2013-04-23 00:00:00.000',0 union all
select 'meter_00001315','2013-04-24 00:00:00.000',13664
select * from [TB]
SELECT meter_code ,
grp AS '连续为0最大开始时间' ,
grp1 AS ' 连续为0最大结束时间' ,
DATEDIFF(dd, grp, grp1) + 1 AS '天数'
FROM ( SELECT meter_code ,
( SELECT MIN(read_date)
FROM TB AS B
WHERE b.read_date >= a.read_date
AND B.meter_code = A.meter_code
&nb