partition

<!-- 查询恶劣天气 -->
	<select id="searchBadWeather" parameterType="TB_BAD_WEATHER" resultType="TB_BAD_WEATHER">
	SELECT * FROM(
		SELECT t1.*,count(*) over() totalCount,rownum AS ROWNUMBER FROM(
			SELECT 
				tbw.ID,
				tbw.JL_PERSON,
				tbw.JL_TIME,
				tbw.WEATHER_TYPE,
				to_char(tbw.STARTTIME,'YYYY-MM-DD HH24:MI:SS') as STARTTIME,
				to_char(tbw.ENDTIME,'YYYY-MM-DD HH24:MI:SS') as ENDTIME,
				tbw.ROADSECTIONID,
				tbw.START_ZH,
				tbw.END_ZH,
				tbw.REMARK,
				tbw.NOWNJD,
				tbw.MINNJD,
				tbw.HD,
				tbw.ROADSTATUSID,
				tbw.INFOSOURCE,
				tbw.BGPERSON,
				tbw.ISINFOCENTER,
				tbw.XG_PERSON_JG,
				tbw.XG_PERSON,
				tbw.XG_TIME,
				tbw.ISREAD,
				tbw.READ_JG,
				tbw.READER,
				tbw.READ_TIME,
				tbw.WEATHERID,
				tbw.RAIN,
				tbw.JG_CODE,
				tbw.WEATHER_KIND,
				tbw.EVENTNO,
				tbw.SUBEVENTNO,
				tbw.DEL_FLAG,
				tbw.INSERT_ORG,
				tbw.VIEW_TYPE,
				tbw.OVER_STAT,
				tbw.BLOCK_STAT,
				tbw.HISTORYID as HISTORYID,
				tst.SUBC_NAME as JG_CODENAME,
				tr.ROAD_NAME as ROADSECTIONIDNAME,
				row_number() over(partition by tbw.JG_CODE, tbw.WEATHER_KIND, tbw.EVENTNO order by tbw.SUBEVENTNO desc) rn  
			 	FROM TB_BAD_WEATHER tbw
			 	left join TB_ROAD tr on tr.ROAD_CODE = tbw.ROADSECTIONID
				left join TB_SUBC_TABLE tst on tst.SUBC_NO = tbw.JG_CODE
			 	where (tbw.DEL_FLAG is null or tbw.DEL_FLAG = 0)
			 	<if test="ID != 0 ">
					and tbw.ID = #{ID,jdbcType=NUMERIC}
				</if>
			 	<if test="ROADSECTIONID != null and ROADSECTIONID != '' ">
					and tbw.ROADSECTIONID = #{ROADSECTIONID}
				</if>
				<if test="JG_CODE != null and JG_CODE != '0001' and JG_CODE !='' ">
					and tbw.JG_CODE = #{JG_CODE}
				</if>
				<if test="WEATHER_KIND != '' and WEATHER_KIND != null ">
					and tbw.WEATHER_KIND = #{WEATHER_KIND}
			   	</if>
				<if test="JL_TIME_S != null and JL_TIME_S != ''">
					and tbw.STARTTIME >= to_date(#{JL_TIME_S},'yyyy-MM-dd HH24:MI:ss') 
			   	</if>
			   	<if test="JL_TIME_E != null and JL_TIME_E != ''">
					and tbw.STARTTIME <= to_date(#{JL_TIME_E},'yyyy-MM-dd HH24:MI:ss')
			   	</if>
			   	<if test="OVER_STAT != null and OVER_STAT != '' ">
					and tbw.OVER_STAT = #{OVER_STAT}
				</if>
				<!-- <if test='sfjs == "0"'>
					and (tbw.ENDTIME is null or tbw.ENDTIME >= to_date('3000-01-01 01:00:00','yyyy-MM-dd HH24:MI:ss'))
		  		</if>
		  		<if test='sfjs == "1"'>
					and (tbw.ENDTIME is not null and tbw.ENDTIME < to_date('3000-01-01 01:00:00','yyyy-MM-dd HH24:MI:ss'))
		  		</if> -->
		  		order by tbw.STARTTIME desc
			) t1 where t1.rn <=1 and rownum <= #{pos} + #{size} 
		) where ROWNUMBER >#{pos}
	</select>