1 CREATE PROCEDURE `th_line_step_beat_state`(in table_name VARCHAR(255),in line_id INT,in SOUTIME datetime,in DESTIME datetime) 2 BEGIN 3 -- 参数(表名,id,开始时间,结束时间) 4 -- 查询当前数据与下一条数据的时间差 5 SET @sqlcmd = concat('select b.`CODE`,b.`NAME`,b.tm_station_id,b.line_id,d.SOUTIME1,d.DESTIME1, 6 UNIX_TIMESTAMP(d.DESTIME1) - UNIX_TIMESTAMP(d.SOUTIME1),b.qty, 7 case 8 when d.va1=d.v1 and d.va1 = 1 then (UNIX_TIMESTAMP(d.DESTIME1) - UNIX_TIMESTAMP(d.SOUTIME1)) / b.qty 9 end beat, 10 b.qty_tag,d.va1,d.v1, 11 case 12 when d.va1=d.v1 and d.va1 = 1 then ''节拍'' 13 when d.va1 = 1 and d.v1 = 0 then ''工作时间'' 14 when d.va1 = 0 and d.v1 = 1 then ''等待时间'' end remarks 15 from 16 ( 17 select a._NAME,a.SOUTIME1,a.DESTIME1,a.va1,a.v1 18 from ( 19 select info._NAME,info._TIMESTAMP SOUTIME1, 20 lead(info._TIMESTAMP,1) over (PARTITION by info._NAME order by info._TIMESTAMP) DESTIME1, 21 info._VALUE va1, 22 lead(info._VALUE,1) over (PARTITION by info._NAME order by info._TIMESTAMP) v1 23 from ',table_name,' info 24 where info._NAME not like ''%CROSS%'' and 25 info.`_TIMESTAMP` >= ','''',SOUTIME,'''',' 26 and info.`_TIMESTAMP` <= ','''',DESTIME,'''',' 27 ) a where a.DESTIME1 is not null 28 union all 29 (select a._NAME,a.SOUTIME1,a.DESTIME1,a.va1,a.v1 30 from ( 31 select info._NAME,info._TIMESTAMP SOUTIME1, 32 lead(info._TIMESTAMP,1) over (PARTITION by info._NAME order by info._TIMESTAMP) DESTIME1, 33 info._VALUE va1, 34 lead(info._VALUE,1) over (PARTITION by info._NAME order by info._TIMESTAMP) v1 35 from ',table_name ,' info 36 where info._NAME not like ''%CROSS%'' and 37 info.`_TIMESTAMP` >= ','''',SOUTIME,'''',' 38 and info.`_TIMESTAMP` <= ','''',DESTIME,'''',' 39 and info._VALUE = 1 40 ) a where a.DESTIME1 is not null) 41 ) d left join tm_station_step b on b.`CODE` = d._name 42 where b.LINE_ID = ','''',LINE_ID,'''',' 43 order by d._NAME, d.SOUTIME1;'); 44 -- select @sqlcmd; 45 PREPARE stmt FROM @sqlcmd; 46 47 EXECUTE stmt; 48 49 DEALLOCATE PREPARE stmt; 50 51 end
CREATE DEFINER=`root`@`%` PROCEDURE `th_line_step_beat_state`(in table_name VARCHAR(255),in line_id INT,in SOUTIME datetime,in DESTIME datetime)BEGIN-- 参数(表名,产线id,开始时间,结束时间)-- 查询当前数据与下一条数据的时间差 SET @sqlcmd = concat('select b.`CODE`,b.`NAME`,b.tm_station_id,b.line_id,d.SOUTIME1,d.DESTIME1,UNIX_TIMESTAMP(d.DESTIME1) - UNIX_TIMESTAMP(d.SOUTIME1),b.qty,case when d.va1=d.v1 and d.va1 = 1 then (UNIX_TIMESTAMP(d.DESTIME1) - UNIX_TIMESTAMP(d.SOUTIME1)) / b.qty end beat,b.qty_tag,d.va1,d.v1,case when d.va1=d.v1 and d.va1 = 1 then ''节拍'' when d.va1 = 1 and d.v1 = 0 then ''工作时间'' when d.va1 = 0 and d.v1 = 1 then ''等待时间'' end remarksfrom (select a._NAME,a.SOUTIME1,a.DESTIME1,a.va1,a.v1from ( select info._NAME,info._TIMESTAMP SOUTIME1, lead(info._TIMESTAMP,1) over (PARTITION by info._NAME order by info._TIMESTAMP) DESTIME1, info._VALUE va1, lead(info._VALUE,1) over (PARTITION by info._NAME order by info._TIMESTAMP) v1 from ',table_name,' info where info._NAME not like ''%CROSS%'' and info.`_TIMESTAMP` >= ','''',SOUTIME,'''',' and info.`_TIMESTAMP` <= ','''',DESTIME,'''',' ) a where a.DESTIME1 is not null union all(select a._NAME,a.SOUTIME1,a.DESTIME1,a.va1,a.v1from ( select info._NAME,info._TIMESTAMP SOUTIME1, lead(info._TIMESTAMP,1) over (PARTITION by info._NAME order by info._TIMESTAMP) DESTIME1, info._VALUE va1, lead(info._VALUE,1) over (PARTITION by info._NAME order by info._TIMESTAMP) v1 from ',table_name ,' info where info._NAME not like ''%CROSS%'' and info.`_TIMESTAMP` >= ','''',SOUTIME,'''',' and info.`_TIMESTAMP` <= ','''',DESTIME,'''',' and info._VALUE = 1 ) a where a.DESTIME1 is not null)) d left join tm_station_step b on b.`CODE` = d._name where b.LINE_ID = ','''',LINE_ID,'''',' order by d._NAME,d.SOUTIME1;');-- select @sqlcmd;PREPARE stmt FROM @sqlcmd;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
end 标签:info,NAME,union,TIMESTAMP,sql,查询,v1,._,va1 From: https://www.cnblogs.com/RainHouse/p/16666336.html