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

CREATE 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 if(e.`CODE` = e.code1,(select @rownum := @rownum + 1),(select @rownum := 1))  rownum,
     e.`CODE`,e.`NAME`,e.tm_station_id,e.line_id,e.SOUTIME1,e.DESTIME1,
     e.diff_seconds,e.qty,e.beat,e.qty_tag,e.va1,e.v1,e.remarks
     from 
         (select b.`CODE`,lag(b.`CODE`,1) over (PARTITION by b.`CODE` order by d.SOUTIME1) code1,
         b.`NAME`,b.tm_station_id,b.line_id,d.SOUTIME1,d.DESTIME1,
         UNIX_TIMESTAMP(d.DESTIME1) - UNIX_TIMESTAMP(d.SOUTIME1) diff_seconds,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 remarks
         from 
         (
            select a._NAME,a.SOUTIME1,a.DESTIME1,a.va1,a.v1 
            from (
                 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.v1 
             from (
                    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,'''',' 
         group by d.SOUTIME1 
         order by b.`CODE`,d.SOUTIME1) e ');
    -- select @sqlcmd;
    PREPARE stmt FROM @sqlcmd;

    EXECUTE stmt;

    DEALLOCATE PREPARE stmt;

end