连续天数及最大连续时间天数开始时间和结束时间问题

WilliamMoore 2013-04-20 03:29:44

如上图，readinfo表中存着表的读数信息
想求出每块表计的最大连续为0的天数和起始终止时间

上面得出的结果为：

meter_code 连续为0最大开始时间连续为0最大结束时间天数
meter_00001314 2013-04-20 00:00:00.000 2013-04-23 00:00:00.000 4

数据库环境为sql 2000

...全文

371 7 打赏收藏转发到动态举报

写回复

用AI写文章

7 条回复

切换为时间正序

请发表友善的回复…

发表回复

WilliamMoore 2013-04-22

打赏
举报

结贴，给分\(^o^)/~

WilliamMoore 2013-04-22

打赏
举报

引用 5 楼 OrchidCat 的回复:

SQL code?123456789101112131415161718192021222324252627282930313233343536373839404142 SELECT meter_code , grp AS '连续为0最大开始时间' , grp1 AS ' 连续为0最大结束时间' , DATEDIFF(dd, ……

解决了，我自己用了一个比较笨的方法，一会比较下效率，谢谢\(^o^)/~

Mr_Nice 2013-04-21

打赏
举报


 
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
                                AND A.read_data = 0
                                AND NOT EXISTS ( SELECT 1
                                                 FROM   TB AS c
                                                 WHERE  DATEDIFF(dd,
                                                              c.read_date,
                                                              b.read_date) = 1 )
                                AND b.meter_code = meter_code
                    ) AS grp ,
                    ( SELECT    MAX(read_date)
                      FROM      TB AS B
                      WHERE     b.read_date >= a.read_date
                                AND B.meter_code = A.meter_code
                                AND B.read_data = 0
                                AND EXISTS ( SELECT 1
                                             FROM   TB AS c
                                             WHERE  DATEDIFF(dd, c.read_date,
                                                             b.read_date) = 1 
                                                             AND b.read_data = c.read_data)   --这里填个条件即可
                                AND b.meter_code = meter_code
                    ) AS grp1
          FROM      TB AS A
        ) T
WHERE   grp IS NOT NULL
        AND grp1 IS NOT NULL
GROUP BY meter_code ,
        grp ,
        grp1
        
/*
meter_code	连续为0最大开始时间	 连续为0最大结束时间	天数
meter_00001314	2013-04-20 00:00:00.000	2013-04-24 00:00:00.000	5
meter_00001315	2013-04-20 00:00:00.000	2013-04-23 00:00:00.000	4*/

xmniemaosheng 2013-04-20

打赏
举报

1 meter_00001314 2013-04-20 00:00:00.000 2013-04-24 00:00:00.000 5 2 meter_00001314 2013-04-26 00:00:00.000 2013-04-26 00:00:00.000 1 3 meter_00001314 2013-04-29 00:00:00.000 2013-04-29 00:00:00.000 1 4 meter_00001315 2013-04-20 00:00:00.000 2013-04-23 00:00:00.000 4

xmniemaosheng 2013-04-20

打赏
举报

麻烦一点，但总归出结果了。 select * from [TB] create table #t (id bigint identity (1,1),meter_code varchar(100),read_date datetime,rank_code int,rank_code1 int) insert #t (meter_code,read_date) select meter_code,read_date from tb where read_data=0 order by meter_code,read_date update x set rank_code=(case when exists (select id from #t t where t.meter_code=x.meter_code and t.read_date=dateadd(dd,-1,x.read_date) ) then 0 else 1 end ) from #t x update #t set rank_code1=(select sum(rank_code) from #t t where t.id<=x.id) from #t x select rank_code1 as No,meter_code,min(read_date) as From_date,max(read_date) as To_date,datediff(dd,min(read_date),max(read_date))+1 as days from #t group by rank_code1,meter_code order by rank_code1,meter_code,min(read_date) drop table #t

WilliamMoore 2013-04-20

打赏
举报

引用 1 楼 OrchidCat 的回复:

SQL code?123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263if object_id('[TB]') is not null drop table [TB]gocreate tabl……

是连续为0 的，不是所有为0，只要中间有一天中断不为0就不算是连续的，您得的这个表得出来的结果应该是这样的 meter_code 连续为0最大开始时间连续为0最大结束时间天数 meter_00001314 2013-04-20 00:00:00.000 2013-04-24 00:00:00.000 5 meter_00001315 2013-04-20 00:00:00.000 2013-04-23 00:00:00.000 4 这样的结果能得出来吗？谢谢了

Mr_Nice 2013-04-20

打赏
举报

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
                                AND A.read_data = 0
                                AND NOT EXISTS ( SELECT 1
                                                 FROM   TB AS c
                                                 WHERE  DATEDIFF(dd,
                                                              c.read_date,
                                                              b.read_date) = 1 )
                                AND b.meter_code = meter_code
                    ) AS grp ,
                    ( SELECT    MAX(read_date)
                      FROM      TB AS B
                      WHERE     b.read_date >= a.read_date
                                AND B.meter_code = A.meter_code
                                AND B.read_data = 0
                                AND EXISTS ( SELECT 1
                                             FROM   TB AS c
                                             WHERE  DATEDIFF(dd, c.read_date,
                                                             b.read_date) = 1 )
                                AND b.meter_code = meter_code
                    ) AS grp1
          FROM      TB AS A
        ) T
WHERE   grp IS NOT NULL
        AND grp1 IS NOT NULL
GROUP BY meter_code ,
        grp ,
        grp1


/*
meter_code	连续为0最大开始时间	 连续为0最大结束时间	天数
meter_00001314	2013-04-20 00:00:00.000	2013-04-29 00:00:00.000	10
meter_00001315	2013-04-20 00:00:00.000	2013-04-23 00:00:00.000	4*/