<?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.ruoyi.project.zt.mapper.LjQueryMapper">


    <select id="getDcyxList" resultType="com.ruoyi.project.zt.domain.LjDcyx">
        select 	A.jh,
        A.djsd1,
        A.djsd2,
        A.dcmc,
        A.hd,
        A.jcgx,A.ysmc_list yxmc,b.zhmc_list yqcmc from (
        SELECT
        B.jh,
        B.djsd1,
        B.djsd2,
        B.dcmc,
        B.hd,
        B.jcgx,
        LISTAGG ( b.ysmc, ', ' ) WITHIN GROUP ( ORDER BY b.ysmc ) AS ysmc_list
        FROM
        (
        SELECT
        B.jh,
        B.djsd1,
        B.djsd2,
        B.dcmc,
        B.hd,
        B.jcgx,
        A.ysmc
        FROM
        AZF07 B
        LEFT JOIN AZL02 A ON B.jh = A.jh
        AND A.djsd1 > B.djsd1
        AND A.djsd2 &lt; B.djsd2
                      WHERE 1=1


        <if test="jhs!=null">
            and
            <foreach item="jh" collection="jhs"
                     open="(" separator=" or " close=")">
                  B.jh LIKE CONCAT(#{jh}, '%' )
            </foreach>
        </if>


        AND B.fclx = '解释'
        GROUP BY
        B.jh,
        B.djsd1,
        B.djsd2,
        B.dcmc,
        B.hd,
        B.jcgx,
        a.ysmc
        ORDER BY
        B.djsd1
        ) B
        GROUP BY
        B.jh,
        B.djsd1,
        B.djsd2,
        B.dcmc,
        B.hd,
        B.jcgx
        ORDER BY
        B.djsd1 )A

        left join

        (

        SELECT
        B.jh,
        B.djsd1,
        B.djsd2,
        B.dcmc,
        B.hd,
        B.jcgx,
        LISTAGG ( b.zhjs, ', ' ) WITHIN GROUP ( ORDER BY b.zhjs ) AS zhmc_list
        FROM
        (
        SELECT
        B.jh,
        B.djsd1,
        B.djsd2,
        B.dcmc,
        B.hd,
        B.jcgx,
        A.zhjs
        FROM
        AZF07 B
        LEFT JOIN AZF09 A ON B.jh = A.jh
        AND A.djsd1 > B.djsd1
        AND A.djsd2 &lt; B.djsd2
                      WHERE 1=1
        <if test="jhs!=null">
            and
            <foreach item="jh" collection="jhs"
                     open="(" separator=" or " close=")">
                B.jh LIKE CONCAT(#{jh}, '%' )
            </foreach>
        </if>
        AND B.fclx = '解释'
        GROUP BY
        B.jh,
        B.djsd1,
        B.djsd2,
        B.dcmc,
        B.hd,
        B.jcgx,
        a.zhjs
        ORDER BY
        B.djsd1
        ) B
        GROUP BY
        B.jh,
        B.djsd1,
        B.djsd2,
        B.dcmc,
        B.hd,
        B.jcgx
        ORDER BY
        B.djsd1) B on a.jh =b.jh  and  a.djsd1=b.djsd1 and  a.djsd2=b.djsd2 and a.dcmc=b.dcmc and a.hd=b.hd
        order by  jh,DJSD1

    </select>
    <select id="getDcyxList2" resultType="com.ruoyi.project.zt.domain.LjDcyx">

        SELECT B.jh,
        B.djsd1,
        B.djsd2,
        B.dcmc,
        b.HD,
        A.ysmc yxmc,a.DJSD1 yxdjsd1,a.DJSD2 yxdjsd2
        FROM AZF07 B
        LEFT JOIN AZL02 A ON B.jh = A.jh
        AND A.djsd1 > B.djsd1
        AND A.djsd2 &lt; B.djsd2
                      WHERE 1 = 1


        <if test="jhs!=null">
            and
            <foreach item="jh" collection="jhs"
                     open="(" separator=" or " close=")">
                B.jh LIKE CONCAT(#{jh}, '%' )
            </foreach>
        </if>

        <if test="dc!=null">
            and B.dcmc LIKE CONCAT(#{dc}, '%' )
        </if>

        AND B.fclx = '解释'
        GROUP BY B.jh,
        B.djsd1,
        B.djsd2,
        B.dcmc,
        b.HD,
        a.ysmc,a.DJSD1,a.DJSD2
        ORDER BY a.djsd1
    </select>
</mapper>