Oracle 以某字段分组,以某字段排序,取第一条,加分页

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

<select id="selectRealTimeMonitorPaging" parameterType="realTimeMonitorEntity" resultType="realTimeMonitorEntity">
		select
		<include refid="Base_Column_List"/>,
		rownum_ 
		from (select
		<include refid="Base_Column_List"/>,
		row_number() over(order by RSM_ID desc) as rownum_
		from(
			select
			 RSM_ID 
			,RSM_FID
			,(select EMI_NUMBER from EQUIPMENT_INFO where EQUIPMENT_INFO.EMI_FID = RSM_EM_FID) AS RSM_EM_FID
			,RSM_FIRST_VALUE
			,RSM_PRESENT
			,to_char(RSM_COLLECTION_TIME,'yyyy-MM-dd') RSM_COLLECTION_TIME
			,RSM_RELATIVE
			,RSM_X
			,RSM_Y
			,RSM_H
			,RSM_INITIAL_VALUE
			,RSM_CURRENT_VALUE
			,RSM_CURRENT_SOILRESSURE
			,RSM_CUMULATIVE_RAINFALL
			,RSM_TEMPERATURE
			,RSM_HUMIDITY
			,RSM_RAINFALL
			,RSM_TYPE
			,RSM_INITIAL_WATER
			,RSM_CURRENT_WATER
			,RSM_DEPTH
			,RSM_ESTABLISH_PEOPLE
			,to_char(RSM_ESTABLISH_TIME,'yyyy-MM-dd') RSM_ESTABLISH_TIME
			,RSM_DELFLAG
			,(select TMP_NAME from TERM_MEASUREPOINKS where TERM_MEASUREPOINKS.TMP_FID = (select EMI_TMP_FID from EQUIPMENT_INFO where EQUIPMENT_INFO.EMI_FID = RSM_EM_FID)) AS point
			,(select EMI_MODEL from EQUIPMENT_INFO where EQUIPMENT_INFO.EMI_FID = RSM_EM_FID and EMI_DELFLAG='0') AS model
			,(select EMI_NAME from EQUIPMENT_INFO where EQUIPMENT_INFO.EMI_FID = RSM_EM_FID and EMI_DELFLAG='0') AS type
			,row_number() over(partition by RSM_EM_FID order by RSM_COLLECTION_TIME desc) as rn
			from REAL_TIME_MONITOR
			where 
			1=1 and RSM_DELFLAG='0'
			<include refid="where_conditions" />
		)where rn=1
		) row_ WHERE row_.rownum_ between #{startPage} and #{endPage}
	</select>

<select id="selectRealTimeMonitorCount" parameterType="realTimeMonitorEntity" resultType="Integer">
		select count(1) from (
			select
		<include refid="Base_Column_List"/>,
		row_number() over(order by RSM_ID desc) as rownum_
		from(
			select
			 RSM_ID 
			,RSM_FID
			,(select EMI_NUMBER from EQUIPMENT_INFO where EQUIPMENT_INFO.EMI_FID = RSM_EM_FID) AS RSM_EM_FID
			,RSM_FIRST_VALUE
			,RSM_PRESENT
			,to_char(RSM_COLLECTION_TIME,'yyyy-MM-dd') RSM_COLLECTION_TIME
			,RSM_RELATIVE
			,RSM_X
			,RSM_Y
			,RSM_H
			,RSM_INITIAL_VALUE
			,RSM_CURRENT_VALUE
			,RSM_CURRENT_SOILRESSURE
			,RSM_CUMULATIVE_RAINFALL
			,RSM_TEMPERATURE
			,RSM_HUMIDITY
			,RSM_RAINFALL
			,RSM_TYPE
			,RSM_INITIAL_WATER
			,RSM_CURRENT_WATER
			,RSM_DEPTH
			,RSM_ESTABLISH_PEOPLE
			,to_char(RSM_ESTABLISH_TIME,'yyyy-MM-dd') RSM_ESTABLISH_TIME
			,RSM_DELFLAG
			,(select TMP_NAME from TERM_MEASUREPOINKS where TERM_MEASUREPOINKS.TMP_FID = (select EMI_TMP_FID from EQUIPMENT_INFO where EQUIPMENT_INFO.EMI_FID = RSM_EM_FID)) AS point
			,(select EMI_MODEL from EQUIPMENT_INFO where EQUIPMENT_INFO.EMI_FID = RSM_EM_FID and EMI_DELFLAG='0') AS model
			,(select EMI_NAME from EQUIPMENT_INFO where EQUIPMENT_INFO.EMI_FID = RSM_EM_FID and EMI_DELFLAG='0') AS type
			,row_number() over(partition by RSM_EM_FID order by RSM_COLLECTION_TIME desc) as rn
			from REAL_TIME_MONITOR
			where 
			1=1 and RSM_DELFLAG='0'
			<include refid="where_conditions" />
		)where rn=1
			)
	</select>

参考sql

select * from (select 表.*, row_number() over(partition by 以此字段为分组  order by 以此字段排序 desc) rn

from) where rn=1   --表示取第一个
  • 0
    点赞
  • 0
    评论
  • 1
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值