Oracle多表联查分页数据重复出现

一、问题描述

使用Oracle的分页语句进行分页,第一页,没问题,第二页出现重复数据:

第一页的数据:

SELECT *
FROM (
	SELECT u.*,rownum AS r
	FROM (
		SELECT configs.VIDEOSERVICE,configs.VOICESERVICE,configs.IMAGESERVICE,doc.id AS docid,doc.name AS docname,doc.begood,rice.graphicprice,rice.speechprice,rice.videoprice,chart.titleName,hpath.FTPURL || doc.PHOTOID AS ftpurl,luate.score
		FROM HEALTHDOCTOR doc
		LEFT JOIN HealthServiceConfig configs ON doc.id = configs.docid 
		LEFT JOIN HEALTHPRICE rice ON doc.id = rice.docid 
		LEFT JOIN healthTitleChart chart ON doc.titlecode = chart.TITLECODE 
		LEFT JOIN HEALTHFTPPATH hpath ON doc.PATHCODE = hpath.PATHCODE 
		LEFT JOIN (
			SELECT round(AVG(score),1) AS score,docid
			FROM healthevaluate
			GROUP BY docid
		) luate ON doc.id = luate.docid 
			LEFT JOIN (
				SELECT COUNT(*) AS ordersum,docid
				FROM HealthyOrder
				GROUP BY docid
			) orders ON doc.id = orders.docid 
		WHERE rice.GRAPHICPRICE >= 0
	) u
	WHERE rownum < 13
)
WHERE r >= 1

第二页的数据:

SELECT *
FROM (
	SELECT u.*,docid
				FROM HealthyOrder
				GROUP BY docid
			) orders ON doc.id = orders.docid 
		WHERE rice.GRAPHICPRICE >= 0 
	) u
	WHERE rownum < 25
)
WHERE r >= 13

第一页和第二页有数据重复了,排序有问题。

二、解决方法

在最外层加个"order by id"其中这个id是主键就行了:

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!