select
* from (select n.* from (SELECT
m.sid,
@rowno := @rowno + 1 AS rowno ,
m.sub_seconds,
m.MAT_ROOT_NO,
m.L4_STEELGRADE_ID ,
m.MAT_ID,
m.FUR_NO
FROM
(
SELECT
c.SID,
c.MAT_ROOT_NO,
c.DISCH_TIME,
c.YIELD_TIMESTAMP,
c.L4_STEELGRADE_ID,
c.MAT_ID,
TIMESTAMPDIFF( SECOND, STR_TO_DATE( e.DISCH_TIME, '%Y%m%d%H%i%s' ), STR_TO_DATE( c.DISCH_TIME, '%Y%m%d%H%i%s' ) ) sub_seconds ,
c.FUR_NO
FROM
(
SELECT
a.*,
( @i := @i + 1 ) AS ord_num
FROM
(
SELECT
p.SID,
p.MAT_ROOT_NO,
t.DISCH_TIME,
p.YIELD_TIMESTAMP,
p.L4_STEELGRADE_ID ,
p.MAT_ID,
(select FUR_NO
from qgcxm1_tmmhr19 tm where p.MAT_ROOT_NO = tm.IN_MAT_NO AND IN_OUT_FLAG = 2 AND tm.DISCH_TIME=t.DISCH_TIME)FUR_NO
FROM
rzpes_pes_mat_material p
LEFT JOIN (SELECT IN_MAT_NO, max(DISCH_TIME) AS DISCH_TIME
FROM qgcxm1_tmmhr19
WHERE IN_OUT_FLAG = 2
GROUP BY IN_MAT_NO) t ON p.MAT_ROOT_NO = t.IN_MAT_NO
WHERE
p.YIELD_TIMESTAMP > DATE_FORMAT( CURRENT_DATE ( ), '%Y-%m-%d' ) and p.yield_eqpt_id='H21'
) a,
( SELECT @i := 1 ) b
ORDER BY
a.YIELD_TIMESTAMP DESC
) c
LEFT JOIN (
SELECT
a1.*,
( @j := @j + 1 ) AS ord_num
FROM
(
SELECT
p1.SID,
p1.MAT_ROOT_NO,
t1.DISCH_TIME,
p1.YIELD_TIMESTAMP
FROM
rzpes_pes_mat_material p1
LEFT JOIN (SELECT IN_MAT_NO, max(DISCH_TIME) AS DISCH_TIME
FROM qgcxm1_tmmhr19
WHERE IN_OUT_FLAG = 2
GROUP BY IN_MAT_NO) t1 ON p1.MAT_ROOT_NO = t1.IN_MAT_NO
WHERE
p1.YIELD_TIMESTAMP > DATE_FORMAT( DATE_SUB( CURRENT_DATE ( ), INTERVAL 1 DAY ), '%Y-%m-%d' ) and p1.yield_eqpt_id='H21'
) a1,
( SELECT @j := 0 ) d
ORDER BY
a1.YIELD_TIMESTAMP DESC
) e ON c.ord_num = e.ord_num where FUR_NO=1 -- 1为炉次1；2为炉次2；3为炉次3
) m,
( SELECT @rowno := 0 ) t ) n )m where m.rowno in (1,2,3,4,5,6);
