信号灯mysql存储过程计算时间段

最新推荐文章于 2025-09-06 22:56:13 发布

转载最新推荐文章于 2025-09-06 22:56:13 发布 · 313 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/tiancun/p/8490050.html

文章标签：

#数据库

本文介绍了一个用于信号灯状态统计的存储过程，通过该过程可以计算每个信号周期内绿灯、黄灯和红灯的持续时间，并进一步统计指定时间段内的信号状态分布情况。

delimiter $$

drop procedure if exists count_area;
create procedure count_area()
begin
declare done int default false;
declare g time;
declare f time;
declare r time;
declare pre time;
declare cur cursor for select green,flash,red from SignalStatus;
declare continue handler for sqlstate '02000' set done=true;
set pre=SEC_TO_TIME(43200);
open cur;
fetch cur into g,f,r;
while(not done) do
-- select TIME_TO_SEC(g);
update SignalStatus set GreenArea=TIME_TO_SEC(f)-TIME_TO_SEC(g),FlashArea=TIME_TO_SEC(r)-TIME_TO_SEC(f),RedArea=TIME_TO_SEC(g)-TIME_TO_SEC(pre) where green=g;
set pre=r;
if TIME_TO_SEC(g)>=TIME_TO_SEC('13:00:23') then
set done=true;
end if;
fetch cur into g,f,r;
end while;
close cur;
select * from SignalStatus;
end;
$$

call count_area();
$$

delimiter ;

区间段统计

select * from SignalStatus;
select TIME_TO_SEC('12:00:00');
set @st='12:40:00';
set @et='12:50:00';
select CONCAT(@st,'-',@et) as 'Period',sum(RedArea),sum(GreenArea),sum(FlashArea),count(1) as 'Times' from SignalStatus where TIME_TO_SEC(green)>=TIME_TO_SEC(@st) and TIME_TO_SEC(green)<=TIME_TO_SEC(@et);

转载于:https://www.cnblogs.com/tiancun/p/8490050.html