SQL查询连续数字并且统计连续个数和分钟

悠然悠哉 2014-11-06 04:50:36

各位大神，业务大概是这样，统计出来一共停靠多少次，h_id连续为一次，并且算出本次停靠的时间多少分钟？（例如：h_id:4到5是一次停靠，停靠时间为10秒，34到43为一次停靠，停靠时间为：1分30秒），请问这种用SQL可以实现吗？求帮助。。。在线等。。。。

...全文

1114 12 打赏收藏转发到动态举报

写回复

用AI写文章

12 条回复

切换为时间正序

请发表友善的回复…

发表回复

axie1999 2017-04-10

打赏
举报

with tmp as ( SELECT 1 id,'1' time FROM dual union all SELECT 2 id,'2' time FROM dual union all SELECT 3 id,'3' time FROM dual union all SELECT 4 id,'4' time FROM dual union all SELECT 7 id,'5' time FROM dual union all SELECT 9 id,'6' time FROM dual union all SELECT 10 id,'7' time FROM dual union all SELECT 11 id,'8' time FROM dual) SELECT id,time FROM tmp a WHERE not exists(SELECT 1 from tmp b WHERE a.id = b.id -1)

kkoofire 2016-01-26

打赏
举报

select c.group_id '断点',datediff(ss,min(c.time),max(c.time)) '停靠次数' from (select x.id,x.time,min(y.id) group_id from @tmp x left join (select id,time from @tmp a where not exists(select 1 from @tmp b where b.id=a.id+1)) y on x.id<=y.id group by x.id,x.time) c group by c.group_id 其中@tmp是原表的表名

悠然悠哉 2014-11-07

打赏
举报

引用 9 楼 sibiyellow 的回复:

下班了没时间啦暂写道这


create table a(id int,time datetime)
insert into a 
select 1,'2014-10-01 05:00:06' union all
select 2,'2014-10-01 05:00:26' union all
select 4,'2014-10-01 05:10:06' union all
select 6,'2014-10-01 05:11:06' union all
select 7,'2014-10-01 05:11:20' union all
select 8,'2014-10-01 05:11:30' union all
select 10,'2014-10-01 05:14:06'

Create proc d_gettime
as
begin
	create table #tb(beginid int,endid int,duration int)
	declare @begin int
	declare @begintime datetime
	declare @changebegin int
	declare @end int
	declare @endtime datetime
	set @begin=0
	set @changebegin=0
	declare @item int
	declare @itemvalue datetime
	declare @i int
	set @i=0
	declare mycursor cursor for select id,time from a order by id
	open mycursor
	fetch next from mycursor into @item,@itemvalue
	while (@@fetch_status=0)
	begin
		if(@i=0)
		begin
			set @begin=@item
			set @begintime=@itemvalue
		end
		if(@item<>@changebegin+1 and @changebegin<>0)
		begin
			if(@end-@begin>0)
			begin
				insert into #tb select @begin,@end,DATEDIFF(SS,@begintime,@endtime)
			end
			set @begin=@item
			set @begintime=@itemvalue
		end
		else
		begin
			set @end=@item
			set @endtime=@itemvalue
		end
		set @changebegin=@item
		set @i=@i+1
		fetch next from mycursor into @item,@itemvalue
	end
	close mycursor
	deallocate mycursor	
	select * from #tb
end
--执行存储过程
exec d_gettime
--结果
beginid     endid       duration
----------- ----------- -----------
1           2           20
6           8           24

嗨咯，在吗？

天下如山 2014-11-06

打赏
举报

下班了没时间啦暂写道这


create table a(id int,time datetime)
insert into a 
select 1,'2014-10-01 05:00:06' union all
select 2,'2014-10-01 05:00:26' union all
select 4,'2014-10-01 05:10:06' union all
select 6,'2014-10-01 05:11:06' union all
select 7,'2014-10-01 05:11:20' union all
select 8,'2014-10-01 05:11:30' union all
select 10,'2014-10-01 05:14:06'

Create proc d_gettime
as
begin
	create table #tb(beginid int,endid int,duration int)
	declare @begin int
	declare @begintime datetime
	declare @changebegin int
	declare @end int
	declare @endtime datetime
	set @begin=0
	set @changebegin=0
	declare @item int
	declare @itemvalue datetime
	declare @i int
	set @i=0
	declare mycursor cursor for select id,time from a order by id
	open mycursor
	fetch next from mycursor into @item,@itemvalue
	while (@@fetch_status=0)
	begin
		if(@i=0)
		begin
			set @begin=@item
			set @begintime=@itemvalue
		end
		if(@item<>@changebegin+1 and @changebegin<>0)
		begin
			if(@end-@begin>0)
			begin
				insert into #tb select @begin,@end,DATEDIFF(SS,@begintime,@endtime)
			end
			set @begin=@item
			set @begintime=@itemvalue
		end
		else
		begin
			set @end=@item
			set @endtime=@itemvalue
		end
		set @changebegin=@item
		set @i=@i+1
		fetch next from mycursor into @item,@itemvalue
	end
	close mycursor
	deallocate mycursor	
	select * from #tb
end
--执行存储过程
exec d_gettime
--结果
beginid     endid       duration
----------- ----------- -----------
1           2           20
6           8           24