<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.com.victorysoft.business.calc.dao.WellTubeDataSyncDao">

  <select id="selectList" resultType="WellTube" fetchSize="20000">
    select t1.jh,
    t1.ny,
    t1.zscw,
    case when t1.scsj > 24 and rzsl >= 0.5 then '开井' else '停井' end kgzt,
    (select dmmc from fla15 where dm = t1.bzdm) tzyy,
    t2.ygcd,
    t2.ygxrrq,
    t2.fgqlx
    from (select jh,
    to_char(#{endDate}, 'yyyymm') ny,
    max(zscw) zscw,
    sum(scsj) scsj,
    sum(rzsl) rzsl,
    max(bzdm) bzdm
    from ys_dba02 t
    where rq between #{startDate} and #{endDate}
    group by jh) t1
    left join (select a.jh,
    sum(case when a.gzxl = 0 and b.lb = '油管' then cd end) ygcd,
    max(a.sgrq) ygxrrq,
    listagg(case when b.lb = '封隔器' then a.gjdm end, ',') within group(order by a.xrxh) fgqlx
    from (select t.*,
    dense_rank() over(partition by jh order by wgrq desc) rn
    from ys_ddh013 t
    where wgrq &lt;= #{endDate}) a
    left join fla55 b on a.gjdm = b.dm
    where rn = 1 group by a.jh) t2 on t1.jh = t2.jh
  </select>

  <select id="selectFla55" resultType="Fla55">
    select * from fla55
  </select>

</mapper>