<?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.DjdcInfoMapper">


	<select id="getList" resultType="com.ruoyi.project.zt.domain.DjDcInfo">



		select 	A.jh,
		A.djsd1,
		A.djsd2,
		A.dcmc,
		A.hd,
		A.jcgx,A.ysmc_list,b.zhmc_list 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
					  B.jh LIKE concat(#{jh},'%')
		AND B.fclx = #{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
					  B.jh LIKE concat(#{jh},'%')
		AND B.fclx = #{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 ;


	</select>
	<select id="ljListByJh" resultType="com.ruoyi.project.zt.domain.Ljinfo">

		SELECT a.jh, a.jkhzb, a.jkzzb, a.jdhzb, a.jdzzb
		FROM JSBA a
		INNER JOIN JSBA b ON b.jh = #{jh}
		WHERE
		ABS(a.jdhzb - b.jdhzb) &lt; 1000  -- 按井底坐标选
		AND
		ABS(a.jdzzb - b.jdzzb) &lt; 1000


	</select>
<!--	&#45;&#45;-->
<!--	&#45;&#45;     ABS(a.jkhzb - b.jkhzb) &lt; 10  &#45;&#45; 按井口坐标-->
<!--	&#45;&#45;     AND-->
<!--	&#45;&#45;     ABS(a.jkzzb - b.jkzzb) &lt; 10;-->
    <select id="getDjjcList" resultType="com.ruoyi.project.zt.domain.Djjc">


		select a.jh,a.jdhzb,a.jdzzb,to_char(b.WJRQ,'YYYY-MM-DD') wjrq,c.kc,c.jd,ksjs,c.JS,xh,
		SZDC cw,
		JC,
		jxzs,
		ZTXH,
		PZ1,
		PZ2,
		PZ3,
		PZ4,
		PZ5,
		PZ6,
		PZ7,
		PZ8,
		PZ9,
		PZ10,
		lx,
		Npc,
		WPC,
		YCMSQK,
		ZCMSQK,
		Qzyy,
		zb,pl,LGBY,cc ztcc
		from JSBA a
		left join jsaa b on a.jh = b.jh
		left join (
		SELECT
		jh,kc,(CASE WHEN kc = 1 THEN 0 ELSE LAG_JS END) ksjs,js, CONCAT( CASE WHEN kc = 1 THEN '0-' ELSE LAG_JS || '-'END,JS) AS jd
		FROM (
		SELECT
		jh, ROW_NUMBER() OVER (PARTITION BY jh ORDER BY js) AS kc,
		JS,
		LAG(JS, 1, 0) OVER (PARTITION BY jh ORDER BY js) AS LAG_JS
		FROM JSDB
		) t) c on a.jh=c.jh
		left join (
		select jh,xh,
		SZDC,
		JC,
		jxzs,
		ZTXH,
		PZ1,
		PZ2,
		PZ3,
		PZ4,
		PZ5,
		PZ6,
		PZ7,
		PZ8,
		PZ9,
		PZ10,
		lx,
		Npc,
		WPC,
		YCMSQK,
		ZCMSQK,
		Qzyy,
		zzjs,
		ROUND(jc / case when (JCSJHJ / 24) =0 then 1 else (JCSJHJ / 24) end , 2) zb
		,pl,LGBY,cc
		from JSHA

		order by xh
		) d on a.JH=d.jh and d.zzjs>ksjs and  d.zzjs&lt;=js

		where 1=1

		<if test="jdhzb!=null">
			  and ABS(#{jdhzb} - a.jdhzb) &lt; #{jl}
		</if>
		<if test="jdzzb!=null">
			AND ABS(#{jdzzb} - a.jdzzb) &lt; #{jl}
		</if>
		<if test="wjsjks!=null and wjsjks!=''">
			and to_char(b.WJRQ,'YYYY-MM-DD')>=#{wjsjks}
		</if>
		<if test="wjsjjs!=null and wjsjjs!=''">
			and to_char(b.WJRQ,'YYYY-MM-DD')&lt;=#{wjsjjs}
		</if>
		<if test="jhs!=null">
			and a.jh in
			<foreach item="jh" collection="jhs"
					 open="(" separator="," close=")">
				#{jh}
			</foreach>
		</if>

		order by kc asc,zb desc
	</select>
	<select id="getZqshfxList" resultType="com.ruoyi.project.zt.domain.DjZqsjfx">


		select a.jh,c.kc,ksjs,c.js,to_char((case when kc ='1' then b.KZRQ1 when kc ='2' then  b.KZRQ2 when kc ='3' then  b.KZRQ3  when kc ='4' then  b.KZRQ4 when kc ='5' then  b.KZRQ5 else  null end ),'YYYY-MM-DD')   || ' ' ||
		TO_CHAR(TO_DATE((case when kc ='1' then b.KZSJ1 when kc ='2' then  b.KZSJ2 when kc ='3' then  b.KZSJ3  when kc ='4' then  b.KZSJ4 when kc ='5' then  b.KZSJ5 else  null end ), 'HH24MI'), 'HH24:MI') || ':00' AS  kssj
		,to_char((case when kc ='1' then b.KZRQ2 when kc ='2' then  (case when KZRQ3 is null or KZRQ3 ='' then WJRQ  else KZRQ3 end )  when kc ='3' then  (case when KZRQ4 is null or KZRQ4 ='' then WJRQ  else KZRQ4 end )  when kc ='4' then   (case when KZRQ5 is null or KZRQ5 ='' then WJRQ  else KZRQ5 end ) when kc ='5' then  b.WJRQ else  null end ),'YYYY-MM-DD') || ' ' ||
		TO_CHAR(TO_DATE((case when kc ='1' then b.KZSJ2 when kc ='2' then  (case when KZRQ3 is null or KZRQ3 ='' then WJSJ  else KZSJ3 end )  when kc ='3' then  (case when KZRQ4 is null or KZRQ4 ='' then WJSJ  else KZSJ4 end )  when kc ='4' then   (case when KZRQ5 is null or KZRQ5 ='' then WJSJ  else KZSJ5 end ) when kc ='5' then  b.WJSJ else  null end ), 'HH24MI'), 'HH24:MI') || ':00' AS  JSSJ
		from JSBA a
		left join jsaa b on a.jh = b.jh
		left join (
		select * from (SELECT jh,
		0                                                AS kc,
		JS,
		LAG(JS, 1, 0) OVER (PARTITION BY jh ORDER BY js) AS ksjs
		FROM JSDB jsdb
		where jsdb.tgcc like '%导管%'
		UNION all
		SELECT jh,
		ROW_NUMBER() OVER (PARTITION BY jh ORDER BY js)  AS kc,
		JS,
		LAG(JS, 1, 0) OVER (PARTITION BY jh ORDER BY js) AS ksjs
		FROM JSDB jsdb
		where jsdb.tgcc not like '%导管%'
		order by jh))  c  on a.jh=c.jh

		where 1=1
		<if test="jdhzb!=null">
			and ABS(#{jdhzb} - a.jdhzb) &lt; #{jl}
		</if>
		<if test="jdzzb!=null">
			AND ABS(#{jdzzb} - a.jdzzb) &lt; #{jl}
		</if>
		<if test="wjsjks!=null and wjsjks!=''">
			and to_char(b.WJRQ,'YYYY-MM-DD')>=#{wjsjks}
		</if>
		<if test="wjsjjs!=null and wjsjjs!=''">
			and to_char(b.WJRQ,'YYYY-MM-DD')&lt;=#{wjsjjs}
		</if>
		<if test="jhs!=null">
			and a.jh in
			<foreach item="jh" collection="jhs"
					 open="(" separator="," close=")">
				#{jh}
			</foreach>
		</if>


		order by kc,jh

	</select>
	<select id="getJcAndCw" resultType="com.ruoyi.project.zt.domain.DjZqsjfx">

		select jh,sum(jc) jc ,LISTAGG ( SZDC, '- ' ) WITHIN GROUP ( ORDER BY xh ) AS cw
		from JSHA a
		where 1=1
		<if test="jh!=null and jh!=''">
			and a. jh =#{jh}
		</if>
		<if test="ksjs!=null">
			and zzjs > #{ksjs}
		</if>
		<if test="js!=null">
			and zzjs &lt;= #{js}
		</if>
		group by jh
	</select>
	<select id="getZjzhfxList" resultType="com.ruoyi.project.zt.domain.DjZjzhfx">

		select a.jh,
		c.kc,
		c.jd,
		ksjs,
		c.JS,
		d.ZJZH,d.ZJJD,zj.zjzhmx,zj.tsgj
		from JSBA a
		left join jsaa b on a.jh = b.jh
		left join (SELECT jh,
		kc,
		(CASE WHEN kc = 1 THEN 0 ELSE LAG_JS END)                        ksjs,
		js,
		CONCAT(CASE WHEN kc = 1 THEN '0-' ELSE LAG_JS || '-' END, JS) AS jd
		FROM (SELECT jh,
		ROW_NUMBER() OVER (PARTITION BY jh ORDER BY js)  AS kc,
		JS,
		LAG(JS, 1, 0) OVER (PARTITION BY jh ORDER BY js) AS LAG_JS
		FROM JSDB) t) c on a.jh = c.jh
		left join JSFA d on a.JH=d.JH and c.kc=d.XH
		left join lj_zjzhfx zj on a.JH=zj.JH and c.kc=zj.kc
		where 1 = 1
		<if test="jdhzb!=null">
			and ABS(#{jdhzb} - a.jdhzb) &lt; #{jl}
		</if>
		<if test="jdzzb!=null">
			AND ABS(#{jdzzb} - a.jdzzb) &lt; #{jl}
		</if>
		<if test="wjsjks!=null and wjsjks!=''">
			and to_char(b.WJRQ,'YYYY-MM-DD')>=#{wjsjks}
		</if>
		<if test="wjsjjs!=null and wjsjjs!=''">
			and to_char(b.WJRQ,'YYYY-MM-DD')&lt;=#{wjsjjs}
		</if>
		<if test="jhs!=null">
			and a.jh in
			<foreach item="jh" collection="jhs"
					 open="(" separator="," close=")">
				#{jh}
			</foreach>
		</if>

		order by kc, jh

	</select>
	<select id="getJshaJc" resultType="com.ruoyi.project.zt.domain.DjZjzhfx">
		select jh,
			   nvl(sum(jc),0)     jc,
			   nvl(ROUND(sum(JCSJHJ)/24,2),0) jcsjhj
		from JSHA
		where jh = #{jh}
		  and zzjs > #{ksjs}
		  and zzjs &lt;= #{js}
		group by jh

	</select>
	<select id="getDjinfoByjh" resultType="com.ruoyi.project.zt.domain.Djjc">

		select JDHZB,JDZZB from JSBA a where a.JH =#{jh}

	</select>
	<select id="getLjjwList" resultType="com.ruoyi.project.zt.domain.Ljjw">
		select  distinct
			* from (
		select *
		from (SELECT a.jh,
					 a.jkhzb,
					 a.jkzzb,
					 a.jdhzb,
					 a.jdzzb,
					 a.jx,
					 a.jb,
					 b.wjjs,
					 b.wjczjs,
					 b.wzcw,
					 kc.kc,
					 zjzq.wjzq,
					 zjzq.zjzq,
					 round(power(power(ABS(a.jkhzb - ${jkhzb}
										   ), 2) + power(ABS(a.jkzzb - ${jkzzb}), 2), 0.5), 2) as jkjl,
					 round(power(power(ABS(a.jdhzb - ${jdhzb}
										   ), 2) + power(ABS(a.jdzzb - ${jdzzb}), 2), 0.5), 2) as jdjl,
					 ABS(a.jkzzb - ${jkzzb})                                                   as jkzjl,
					 ABS(a.jkhzb - ${jkhzb})                                                   as jkhjl,
					 ABS(a.jdzzb - ${jdzzb})                                                   as jdzjl,
					 ABS(a.jdhzb - ${jdhzb})                                                   as jdhjl
			  FROM JSBA a
					   left join jsaa b
								 on a.jh = b.jh

					   left join
				   (select jsta.jh,
						   sum(case when jsta.sgzyxm = '完井作业' then jsta.sjts else 0 end)                  as wjzq,
						   sum(jsta.sjts) - sum(case when jsta.sgzyxm = '完井作业' then jsta.sjts else 0 end) as zjzq
					FROM JSTA jsta
					where jsta.jd1 is not null
					   or jsta.jd2 is not null
					group by jsta.jh) zjzq
				   on zjzq.jh = a.jh

					   left join (select jh, count(*) as kc
								  from jsdb
								  where tgcc not like '%导管%'
								  group by jh) kc
								 on a.jh = kc.jh

			  WHERE 1 = 1
				and a.jh not like '%侧%'
				and ABS(a.jdhzb - #{jdhzb}) &lt; #{jl}
				AND ABS(a.jdzzb - #{jdzzb}) &lt; #{jl}

				and TO_CHAR(b.wjrq, 'YYYY') between #{wjsjks} and #{wjsjjs}
			  <if test="qk!=null and qk!=''">
				  and b.qk like CONCAT(CONCAT('%', #{qk}), '%')
			  </if>
			<if test="js!=null">
				and b.wjjs &lt;= #{js}
			</if>
			<if test="jxs != null and jxs.size() > 0">
				AND a.jx IN
				<foreach item="item" collection="jxs" open="(" separator="," close=")">
					#{item}
				</foreach>
			</if>
			<if test="jbs != null and jbs.size() > 0">
				AND a.jb IN
				<foreach item="item" collection="jbs" open="(" separator="," close=")">
					#{item}
				</foreach>
			</if>
			  order by jkjl asc) where ROWNUM &lt;= 5
		union all
		select *
		from (
				 SELECT a.jh,
						a.jkhzb,
						a.jkzzb,
						a.jdhzb,
						a.jdzzb,
						a.jx,
						a.jb,
						b.wjjs,
						b.wjczjs,
						b.wzcw,
						kc.kc,
						zjzq.wjzq,
						zjzq.zjzq,
						round(power(power(ABS(a.jkhzb - ${jkhzb}
											  ), 2) + power(ABS(a.jkzzb - ${jkzzb}), 2), 0.5), 2) as jkjl,
						round(power(power(ABS(a.jdhzb - ${jdhzb}
											  ), 2) + power(ABS(a.jdzzb - ${jdzzb}), 2), 0.5), 2) as jdjl,
						ABS(a.jkzzb - ${jkzzb})                                                   as jkzjl,
						ABS(a.jkhzb - ${jkhzb})                                                   as jkhjl,
						ABS(a.jdzzb - ${jdzzb})                                                   as jdzjl,
						ABS(a.jdhzb - ${jdhzb})                                                   as jdhjl
				 FROM JSBA a
						  left join jsaa b
									on a.jh = b.jh
						  left join
					  (select jsta.jh,
							  sum(case when jsta.sgzyxm = '完井作业' then jsta.sjts else 0 end)                  as wjzq,
							  sum(jsta.sjts) - sum(case when jsta.sgzyxm = '完井作业' then jsta.sjts else 0 end) as zjzq
					   FROM JSTA jsta
					   where jsta.jd1 is not null
						  or jsta.jd2 is not null
					   group by jsta.jh) zjzq
					  on zjzq.jh = a.jh

						  left join (select jh, count(*) as kc
									 from jsdb
									 where tgcc not like '%导管%'
									 group by jh) kc
									on a.jh = kc.jh

				 WHERE 1 = 1
				   and a.jh not like '%侧%'
				   and ABS(a.jdhzb - #{jdhzb}) &lt; #{jl}
				   AND ABS(a.jdzzb - #{jdzzb}) &lt; #{jl}

				   and TO_CHAR(b.wjrq, 'YYYY') between #{wjsjks} and #{wjsjjs}
					<if test="qk!=null and qk!=''">
						and b.qk like CONCAT(CONCAT('%', #{qk}), '%')
					</if>
					<if test="js!=null">
						and b.wjjs &lt;= #{js}
					</if>
				<if test="jxs != null and jxs.size() > 0">
					AND a.jx IN
					<foreach item="item" collection="jxs" open="(" separator="," close=")">
						#{item}
					</foreach>
				</if>
			<if test="jbs != null and jbs.size() > 0">
				AND a.jb IN
				<foreach item="item" collection="jbs" open="(" separator="," close=")">
					#{item}
				</foreach>
			</if>
				 order by jdjl asc

			 ) where ROWNUM &lt; =5
			   ) a order by jh


	</select>
<!--	select ss.kc,ss.ztxh,ss.cc,-->
<!--	count( *) as ztsl,-->
<!--	round( sum(case when ss.kc=1 and tcyk=1  then 1 else 0 end )/count(*)*100,2)as ytzl,-->
<!--	round( sum(case when ss.kc=2 and tcyk=1 then 1 else 0 end )/count(*)*100,2) as etzl,-->
<!--	round( sum(case when ss.kc=3 and tcyk=1 then 1 else 0 end )/count(*)*100,2) as stzl,-->
<!--	round( sum(case when ss.kc=4 and tcyk=1 then 1 else 0 end )/count(*)*100,2) as sitzl,-->
<!--	round( sum(case when ss.kc=5 and tcyk=1 then 1 else 0 end )/count(*)*100,2) as wtzl-->
<!--	from (-->
<!--	select ss.kc,ss.lag_js,ss.js,jsha.ztxh,jsha.cc,jsha.zzjs,nvl(jsha.qsjs,0),-->
<!--	-&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;判断是否在开次内一趟-->
<!--	case when  abs(nvl(jsha.qsjs,0)-ss.lag_js) &lt;=30 and jsha.zzjs=ss.js then 1 else 0 end as tcyk-->
<!--	from (-->
<!--	-&#45;&#45;&#45;&#45;&#45;&#45;获取合适的井组以及井的开次信息以及井深信息-->
<!--	select jh ,-->
<!--	ROW_NUMBER() OVER (PARTITION BY jh ORDER BY js) AS kc,-->
<!--	JS,-->
<!--	LAG(JS, 1, 0) OVER (PARTITION BY jh ORDER BY js) AS LAG_JS-->
<!--	from JSDB-->
<!--	where jh in(-->
<!--	select jh from jsaa where qk like  CONCAT(CONCAT('%', #{qk}), '%')  and wjrq >sysdate-365*2-->
<!--	) and tgcc not like '%导管%'-->
<!--	-&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;-->
<!--	) ss-->
<!--	right join (select * from jsha where jh in(select jh from jsaa where qk like CONCAT(CONCAT('%', #{qk}), '%') and wjrq >sysdate-365*2))jsha-->
<!--	on jsha.jh=ss.jh  and jsha.zzjs between ss.lag_js and ss.js-->
<!--	)ss-->
<!--	group by ss.kc,ss.ztxh,ss.cc-->
	<select id="getDjZtfxList" resultType="com.ruoyi.project.zt.domain.DjZtfx">

		select ss.kc,ss.ztxh,ss.cc,ss.cj,
		count( *) as ztsl,
		round( sum(case when tcyk=1  then 1 else 0 end )/count(*)*100,2)as ytzl,
		-- round( sum(case when ss.kc=1 and tcyk=1  then 1 else 0 end )/count(*)*100,2)as ytzl,
		--round( sum(case when ss.kc=2 and tcyk=1 then 1 else 0 end )/count(*)*100,2) as etzl,
		--  round( sum(case when ss.kc=3 and tcyk=1 then 1 else 0 end )/count(*)*100,2) as stzl,
		-- round( sum(case when ss.kc=4 and tcyk=1 then 1 else 0 end )/count(*)*100,2) as sitzl,
		-- round( sum(case when ss.kc=5 and tcyk=1 then 1 else 0 end )/count(*)*100,2) as wtzl,
		MEDIAN(nvl(jc,0)) as jc_zw,max(nvl(jc,0)) as jc_max,MEDIAN(nvl(jxzs,0)) as jxzs_zw,max(nvl(jxzs,0)) as jxzs_max,MEDIAN(nvl(qsjs,0)) as qsjs_zw
		from (
		select ss.kc,ss.lag_js,ss.js,jsha.cj,jsha.ztxh,jsha.cc,jsha.zzjs,jsha.jc,jsha.jxzs,nvl(jsha.qsjs,0) as qsjs,
		---------判断是否在开次内一趟
		case when  abs(nvl(jsha.qsjs,0)-ss.lag_js) &lt;=30 and jsha.zzjs=ss.js then 1 else 0 end as tcyk
		from (
		-------获取合适的井组以及井的开次信息以及井深信息
		select jh ,
		ROW_NUMBER() OVER (PARTITION BY jh ORDER BY js) AS kc,
		JS,
		LAG(JS, 1, 0) OVER (PARTITION BY jh ORDER BY js) AS LAG_JS
		from JSDB
		where jh in(
		select jh from jsaa where qk like CONCAT(CONCAT('%', #{qk}), '%')  and wjrq >sysdate-365*2
		) and tgcc not like '%导管%'
		-------------------------------------------------
		) ss
		right join (select * from jsha where jh in(select jh from jsaa where qk like CONCAT(CONCAT('%', #{qk}), '%')  and wjrq >sysdate-365*2))jsha
		on jsha.jh=ss.jh  and jsha.zzjs between ss.lag_js and ss.js
		)ss
		group by ss.kc,ss.ztxh,ss.cc,ss.cj
	</select>
	<select id="getFdcsList" resultType="com.ruoyi.project.zt.domain.Jsha">
		select a.jh,c.kc,zy, zs, gj, bs, pl, lgby, ztyj, hkyh, cjl, pssd, ztsgl, bsgl, sfsdzg, sfsdzt, gllyl
		from JSBA a
		left join jsaa b on a.jh = b.jh
		left join (
		SELECT
		jh,kc,(CASE WHEN kc = 1 THEN 0 ELSE LAG_JS END) ksjs,js, CONCAT( CASE WHEN kc = 1 THEN '0-' ELSE LAG_JS || '-'END,JS) AS jd
		FROM (
		SELECT
		jh, ROW_NUMBER() OVER (PARTITION BY jh ORDER BY js) AS kc,
		JS,
		LAG(JS, 1, 0) OVER (PARTITION BY jh ORDER BY js) AS LAG_JS
		FROM JSDB
		) t) c on a.jh=c.jh
		left join (
		select jh,xh,
		JC,ZZJS,zy, zs, gj, bs, pl, lgby, ztyj, hkyh, cjl, pssd, ztsgl, bsgl, sfsdzg, sfsdzt, gllyl
		from JSHA

		order by xh
		) d on a.JH=d.jh and d.zzjs>ksjs and  d.zzjs &lt;=js

		where 1=1
		<if test="jhs!=null">
			and a.jh in
			<foreach item="jh" collection="jhs"
					 open="(" separator="," close=")">
				#{jh}
			</foreach>
		</if>

		order by kc asc
	</select>
	<select id="getZqshfxList2" resultType="com.ruoyi.project.zt.domain.DjZqsjfx">
		select a.jh,c.ksjs,c.JS,to_char(KZRQ1,'YYYY-MM-DD') || ' ' ||TO_CHAR(TO_DATE(KZSJ1, 'HH24MI'), 'HH24:MI') kssj,to_char(KZRQ2,'YYYY-MM-DD') || ' ' ||TO_CHAR(TO_DATE(KZSJ2, 'HH24MI'), 'HH24:MI') jssj 	from jsaa a

																																	left join (
			select * from (SELECT jh,
								  0                                                AS kc,
								  JS,
								  LAG(JS, 1, 0) OVER (PARTITION BY jh ORDER BY js) AS ksjs
						   FROM JSDB jsdb
						   where jsdb.tgcc like '%导管%'
						   UNION all
						   SELECT jh,
								  ROW_NUMBER() OVER (PARTITION BY jh ORDER BY js)  AS kc,
								   JS,
								  LAG(JS, 1, 0) OVER (PARTITION BY jh ORDER BY js) AS ksjs
						   FROM JSDB jsdb
						   where jsdb.tgcc not like '%导管%'
						   order by jh))  c  on a.jh=c.jh  where kc='1'
		<if test="jh!=null and jh!=''">
			and a. jh =#{jh}
		</if>
		order by JH




	</select>
	<select id="getZsjList" resultType="com.ruoyi.project.zt.domain.Zsjfx">
		SELECT a.jh jhbm,b.jh,b.scfsmc zrzt,
		round(power(power(ABS(a.jkhzb - #{jkhzb}
		), 2) + power(ABS(a.jkzzb - #{jkzzb}), 2), 0.5), 2) as jkjl,
		round(power(power(ABS(a.jdhzb - #{jdhzb}
		), 2) + power(ABS(a.jdzzb -  #{jdzzb}), 2), 0.5), 2) as jdjl

		FROM JSBA
				 a LEFT JOIN  V_ODS_JCXX_DJKFJCXX@ZS b on a.jh= b.jhbm
		WHERE 1 = 1
		and a.jh not like '%侧%'
		and ABS(a.jkhzb - #{jkhzb}) &lt; #{jl}
		AND ABS(a.jkzzb - #{jkzzb}) &lt; #{jl}

		order by jkjl asc
	</select>
	<select id="getZsjrbList" resultType="com.ruoyi.project.zt.domain.Zsjrb">
		SELECT jh,cwmc,scjdds|| '-' ||scjddsa sdfw,Nvl(yy,0)yy,nvl(pzl,0)pzl,nvl(scsc,0)scsc FROM (select * from V_ODS_CYQ_SCSJ_ZSSCSJ@ZS t where jh=#{jh}  order by t.RQ desc )a WHERE ROWNUM &lt;=1
	</select>
	<select id="getZsjJz" resultType="com.ruoyi.project.zt.domain.Zsjjz">
		SELECT  *FROM(	select jzmc,t.ZQLXMC,t.ZQLXDM
		from V_ODS_CYQ_JCXX_ZQJZJCXX@ZS t where t.jzmc like CONCAT(#{jh}, '%') )a WHERE ROWNUM &lt;=1
	</select>
</mapper>