mysql的with的使用_mysql中with和set一起使用

   with a as ( <foreach collection="list" item="item" separator="union">
                    select
        '${item.nodePlace}'  nodePlace,
        '${item.endordertime}' endordertime,
       '${item.dataTime}'  dataTime,
        '${item.SensorTypeID}' SensorTypeID,
       '${item.maxData}'  maxData,
       '${item.minData}'  minData,
       '${item.alarmId}'  AlarmID,
        '${item.mineId}' mineId,
        '${item.minDataTime}' minDataTime,
       '${item.maxDataTime}'  maxDataTime,
       '${item.startTime}'  startTime,
       '${item.endTime}'  endTime,
       '${item.lastUpdateTime}'  lastUpdateTime,
       '${item.alaReason}'  alaReason,
       '${item.alaproStep}'  alaproStep,
       '${item.sensorUnit}'  sensorUnit,
       '${item.stateId}'  stateId,
       '${item.nodeId}'  nodeId,
        '${item.avgData}' avgData,
        '${item.sensorName}' sensorName,
       '${item.alarmLevel}'  alarmLevel,
        '${item.mineZK}' mineZK,
       '${item.mineZT}'  mineZT,
        '${item.wsGrade}' wsGrade,
       '${item.hydrogeological}'  hydrogeological,
       '${item.fire}'  fire,
       '${item.rockburs}'  rockburs
    </foreach>
        )
        select a.*,
        vs.pathName AS deptParentName,
        k.MineName as mineName,
        k.MineID as mineId,
        k.dept_id as deptId,
        d.dic_name as alarmType,
        ar.AcceptAlarmFlg aracceptAlarmFlg,
        ar.AcceptAlarmPerson arAcceptAlarmPerson,
        ar.AcceptAlarmTime arAcceptAlarmTime,
        ar.Duties arduties,
        ar.Miners arminers,
        ar.Remark arRemark,
        ai.AcceptAlarmFlg aiacceptAlarmFlg,
        ai.AcceptAlarmPerson aiAcceptAlarmPerson,
        ai.AcceptAlarmTime aiAcceptAlarmTime,
        ai.Duties aiduties,
        ai.Miners aiminers,
        ai.Remark aiRemark,
        aim.AcceptAlarmFlg aimacceptAlarmFlg,
        aim.AcceptAlarmPerson aimAcceptAlarmPerson,
        aim.AcceptAlarmTime aimAcceptAlarmTime,
        aim.Duties aimduties,
        aim.Miners aimminers,
        aim.Remark aimRemark,
        aim.File1 aimfile,
        aim.TypeRemark aimtyperemark,
        c.AcceptAlarmFlg acceptAlarmFlg,
        c.AcceptAlarmPerson acceptAlarmPerson,
        c.AcceptAlarmTime acceptAlarmTime,
        c.Remark acceptRemark,
        c.Duties duties,
        c.Miners miners,
        c.SelectRemark selectsemark,
        g.ResponsePerson responsePerson,
        g.ResponseTime responseTime,
        g.Remark respRemark,
        g.MisreportFlg respMisreportFlg,
        h.FalseType falseType,
        h.FalseReason falseReason,
        z.AcceptAlarmPerson mAcceptAlarmPerson,
        z.AcceptAlarmTime mAcceptAlarmTime,
        z.Remark mRemark,
        ca.AcceptAlarmFlg cAcceptAlarmFlg,
        ca.AcceptAlarmPerson cAcceptAlarmPerson,
        ca.AcceptAlarmTime cAcceptAlarmTime,
        ca.Duties cduties,
        ca.Miners cminers,
        ca.Remark caRemark,
        la.AcceptAlarmFlg lAcceptAlarmFlg,
        la.AcceptAlarmPerson lAcceptAlarmPerson,
        la.AcceptAlarmTime lAcceptAlarmTime,
        la.Duties lduties,
        la.Miners lminers,
        la.Remark laRemark,
        dis.DispatchPerson DispatchPerson,
        dis.DispatchTime DispatchTime,
        dis.Remark disRemark,
        dis.File1 disFile1,
        dis.MisreportFlg disMisreportFlg,
        dis.SelectRemark disSelectRemark,
        dip.DisposePeople dipDisposePeople,
        dip.DispatchTime dipDispatchTime,
        dip.Remark  dipRemark,
        cr.ResponsePerson cresponsePerson,
        cr.ResponseTime cresponseTime,
        cr.Remark cremark,
        cr.MisreportFlg cmisreportFlg,
        lr.ResponsePerson lresponsePerson,
        lr.ResponseTime lresponseTime,
        lr.Remark lremark,
        lr.MisreportFlg lmisreportFlg,
        v.VerifyPerson verifyPerson,
        v.VerifyTime verifyTime,
        v.Remark vRemark,
        v.File1 vFile1,
        v.MisreportFlg vMisreportFlg,
        kar.File1 filePathAnaly,
        case
        when n.NodeID is null then a.MaxData
        when n.AlarmUpperValue=0 then a.MinData
        when a.MaxData >= n.AlarmUpperValue then a.MaxData
        when a.MinData &lt;=n.AlarmLowerValue  then a.MinData
        else a.MaxData end Datas,
        case
        when n.NodeID is null then DATE_FORMAT(a.MaxDataTime,'%Y-%m-%d %H:%i:%S')
        when n.AlarmUpperValue=0 then DATE_FORMAT(a.MinDataTime,'%Y-%m-%d %H:%i:%S')
        when a.MaxData >= n.AlarmUpperValue then DATE_FORMAT(a.MaxDataTime,'%Y-%m-%d %H:%i:%S')
        when a.MinData &lt;= n.AlarmLowerValue then  DATE_FORMAT(a.MinDataTime,'%Y-%m-%d %H:%i:%S')
        else DATE_FORMAT(a.MaxDataTime,'%Y-%m-%d %H:%i:%S')  end DataTimes
        from a
        left join v_k_mine_base AS k ON a.mineId = k.MineID
        LEFT JOIN k_dictionary d ON d.dic_code = a.stateId AND d.dic_type = 'nodeAlarmType'
        left join k_analy_report kar on kar.AlarmID = a.AlarmID
        LEFT JOIN k_alarm_accept c ON c.AlarmID = a.AlarmID
        LEFT JOIN k_accept_resident ar on a.AlarmID=ar.AlarmID
        LEFT JOIN k_accept_inspector ai on a.AlarmID=ai.AlarmID
        LEFT JOIN k_accept_immediately aim on a.AlarmID=aim.AlarmID
        LEFT JOIN (SELECT
        *
        FROM
        ( SELECT ROW_NUMBER() OVER ( PARTITION  BY b.AlarmID ORDER BY b.ResponseTime desc ) AS `ROW`,b.* FROM k_alarm_response b  ) a
        WHERE
        a.ROW = 1) g ON g.AlarmID = a.AlarmID
        left join k_false_alarm h on a.AlarmID=h.AlarmID and h.SensorTypeID  in ('0043','0001','0011') and
        h.StateID='001'
        left join k_alarm_accept_mine z on z.AlarmID=a.AlarmID
        LEFT JOIN k_node n on a.NodeID=n.NodeID
        left join v_k_dept_path vs on vs.MineID=a.mineId and vs.pid=#{topId}
        LEFT JOIN k_company_accept ca on a.AlarmID=ca.AlarmID
        LEFT JOIN k_local_accept la on a.AlarmID=la.AlarmID
        LEFT JOIN k_dispatch_accept dis on a.AlarmID=dis.AlarmID
        LEFT JOIN k_dispose_accept dip on a.AlarmID=dip.AlarmID
        LEFT JOIN (SELECT
        *
        FROM
        ( SELECT ROW_NUMBER() OVER ( PARTITION  BY b.AlarmID ORDER BY b.ResponseTime desc ) AS `ROW`,b.* FROM k_company_response b  ) a
        WHERE
        a.ROW = 1) cr on cr.AlarmID=a.AlarmID
        LEFT JOIN (SELECT
        *
        FROM
        ( SELECT ROW_NUMBER() OVER ( PARTITION  BY b.AlarmID ORDER BY b.ResponseTime desc ) AS `ROW`,b.* FROM k_local_response b  ) a
        WHERE
        a.ROW = 1) lr on lr.AlarmID=a.AlarmID
        LEFT JOIN ( SELECT
        t1.*
        FROM
        k_alarm_verify t1 inner join (SELECT MAX( VerifyTime ) AS VerifyTime FROM k_alarm_verify GROUP BY AlarmID ) t2 ON t1.VerifyTime = t2.VerifyTime) v
        ON v.AlarmID = a.AlarmID