Oracle分页获取数据时，数据重复错乱问题

今天测试出的一个bug，差点坑到了，大概场景就是在oracle数据库分页查询时，第一页的数据和第二页的数据重复率达到百分之70以上，开始以为是前端UI控件刷新问题，后面发现其实不是。简单记录下
 

SELECT
	*
FROM
	(
		SELECT
			A .*, ROWNUM RN
		FROM
			(
				SELECT
					TD_TRADE_PARTICIPANT. OID,
					TD_TRADE_PARTICIPANT. NAME,
					TD_TRADE_PARTICIPANT. ACCOUNT,
					TD_TRADE_PARTICIPANT.PHONE,
					TD_TRADE_PARTICIPANT.EMAIL,
					TD_TRADE_PARTICIPANT.COUNTRY,
					TD_TRADE_PARTICIPANT.COUNTRY_CODE,
					TD_TRADE_PARTNER. NAME AS MAIN_UNIT,
					TD_TRADE_PARTICIPANT.USER_ID,
					TD_TRADE_PARTNER. OID AS MAIN_UNIT_CODE,
					TD_TRADE_ORG. NAME AS UNIT,
					TD_TRADE_ORG.CODE AS UNIT_CODE,
					TD_TRADE_ORG.COUNTRY AS ORG_COUNTRY,
					TD_TRADE_ORG.ORG_SHIP,
					TD_TRADE_ORG.ORG_SHIP_CODE,
					TD_TRADE_PARTICIPANT.ACTIVITY_ID,
					TD_TRADE_PARTICIPANT. TYPE,
					TD_TRADE_PARTICIPANT.TYPE_CODE,
					TD_TRADE_PARTICIPANT.STATUS,
					TD_TRADE_PARTICIPANT.IS_VISIBLE,
					TD_TRADE_PARTICIPANT. JOB,
					TD_TRADE_PARTICIPANT.CERTIFICATENUM,
					TD_TRADE_PARTICIPANT.CERTIFICATE_TYPE,
					TD_TRADE_PARTICIPANT.ACCREDITATION_STATUS,
					TD_TRADE_PARTICIPANT.sex,
					TO_CHAR (
						TD_TRADE_PARTICIPANT.SIGN_UP_TIME,
						'yyyy-mm-dd HH24:mi:ss:ff'
					) AS SIGN_UP_TIME
				FROM
					TD_TRADE_PARTICIPANT
				LEFT JOIN TD_TRADE_ORG ON TD_TRADE_ORG. OID = TD_TRADE_PARTICIPANT.UNIT_CODE
				LEFT JOIN TD_TRADE_PARTNER ON TD_TRADE_PARTNER. OID = TD_TRADE_PARTICIPANT.MAIN_UNIT_CODE
				AND TD_TRADE_PARTNER.IS_DELETE = '0'
				WHERE
					TD_TRADE_PARTICIPANT.is_delete = '0'
				AND TD_TRADE_PARTICIPANT.ACTIVITY_ID = 'blt2T3Q-QHOQR4dltwv7qw'
				AND TD_TRADE_PARTICIPANT.STATUS IN ('3', '4')
				ORDER BY
					DECODE (
						TD_TRADE_PARTICIPANT.STATUS,
						'3',
						1,
						'2',
						2,
						'1',
						3,
						'0',
						4,
						'4',
						5
					)
			) A
		WHERE
			ROWNUM <= 20
	)
WHERE
	RN >= 11

 

就是这么个很简单的多表查询，经过检查发现其实是由于后面的order by语句引起的，在后面排序语句上加上主键OID即可解决，具体的原因恐怕得去看oracle的内部设计了