<?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="com.zjrs.ggfw.rlzysc.entity.ShzsMapper">
    <select id="getShzsList" resultType="com.zjrs.ggfw.rlzysc.dto.JlxxShzsUDTO">
     select a.rs_rlzy_shzs_dyb_lsh dyblsh,a.rs_rlzy_shzs_lsh rsrlzyshzslsh,b.ZSMC zsmc,b.HQSJ hqsj,b.ZSBH zsbh,b.XXMS xxms
      from v_rs_rlzy_shzs_dyb a,v_rs_rlzy_shzs b where
     a.rs_rlzy_jlxx_lsh = #{dto.rsrlzyjlxxlsh} and a.rs_rlzy_shzs_lsh = b.rs_rlzy_shzs_lsh
        order by b.ts_xgsj desc
    </select>

    <select id="getShzspoList" parameterType="com.zjrs.ggfw.rlzysc.dto.grzxjlxx.ZjDTO" resultType="com.zjrs.ggfw.rlzysc.po.RsRlzyShzsPO">
        select b.*,a.rs_rlzy_shzs_dyb_lsh dyblsh
        from v_rs_rlzy_shzs_dyb a,v_rs_rlzy_shzs b where
        a.rs_rlzy_jlxx_lsh = #{dto.zjlsh}
        and a.rs_rlzy_shzs_lsh = b.rs_rlzy_shzs_lsh
        order by b.ts_xgsj desc
    </select>

    <select id="getShzsNrById" resultType="com.zjrs.ggfw.rlzysc.po.RsRlzyShzsPO">
        select * from v_rs_rlzy_shzs c where c.rs_rlzy_shzs_lsh not in
       (select a.rs_rlzy_shzs_lsh from v_rs_rlzy_shzs_dyb a,v_rs_rlzy_jlxx b
       where a.rs_rlzy_jlxx_lsh = b.rs_rlzy_jlxx_lsh and a.rs_rlzy_jlxx_lsh = #{jlxxlsh}
       and b.rs_yhlsh = #{yhlsh}) and c.rs_yhlsh = #{yhlsh} 
    </select>

    <select id="getshzsWhList" resultType="com.zjrs.ggfw.rlzysc.po.RsRlzyShzsPO">
        select * from v_rs_rlzy_shzs c where c.rs_rlzy_shzs_lsh not in
       (select a.rs_rlzy_shzs_lsh from v_rs_rlzy_shzs_dyb a,v_rs_rlzy_jlxx b
       where a.rs_rlzy_jlxx_lsh = b.rs_rlzy_jlxx_lsh and a.rs_rlzy_jlxx_lsh =  #{dto.rsrlzyjlxxlsh}
       and b.rs_yhlsh = #{yhlsh}) and c.rs_yhlsh = #{yhlsh} order by c.ts_xgsj desc
    </select>
</mapper>
