Oracle使用null字段排序分页

现象：列表排序字段为空时会使排序紊乱

SELECT
	* 
FROM
	(
	SELECT
		tmp_page.*,
		ROWNUM row_id 
	FROM
		(
		SELECT
		*
		FROM
			TR_TASK_PUBLISH 
		WHERE
			 TASK_ID = '1000021'
		ORDER BY COMMIT_TIME DESC 
		) tmp_page 
	WHERE
		ROWNUM <= 20
	) 
WHERE
	row_id > 10;
	
	
	SELECT
	* 
FROM
	(
	SELECT
		tmp_page.*,
		ROWNUM row_id 
	FROM
		(
		SELECT
		*
		FROM
			TR_TASK_PUBLISH 
		WHERE
			 TASK_ID = '1000021'
		ORDER BY COMMIT_TIME DESC
		) tmp_page 
	WHERE
		ROWNUM <= 10
	) 
WHERE
	row_id > 0;

 

 如图：id为9和10的数据重复出现。

1.解决

        排序时追加一个唯一的健

SELECT
	* 
FROM
	(
	SELECT
		tmp_page.*,
		ROWNUM row_id 
	FROM
		(
		SELECT
		*
		FROM
			TR_TASK_PUBLISH 
		WHERE
			 TASK_ID = '1000021'
		ORDER BY COMMIT_TIME DESC ,id ASC 
		) tmp_page 
	WHERE
		ROWNUM <= 20
	) 
WHERE
	row_id > 10;
	
	
	SELECT
	* 
FROM
	(
	SELECT
		tmp_page.*,
		ROWNUM row_id 
	FROM
		(
		SELECT
		*
		FROM
			TR_TASK_PUBLISH 
		WHERE
			 TASK_ID = '1000021'
		ORDER BY COMMIT_TIME DESC,id ASC 
		) tmp_page 
	WHERE
		ROWNUM <= 10
	) 
WHERE
	row_id > 0;