MySql使用存储过程拼接sql，查询当前数据与下一条数据时间差，union all合并查询结果集

标签：info NAME union TIMESTAMP sql 查询 v1 ._ va1

 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