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

﹏ヽ暗。殇╰゛Y 2022-06-05 08:38 236阅读 0赞

**一、问题描述**

使用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

![20171122170537360][]

第二页的数据:

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 < 25
    )
    WHERE r >= 13

![20171122170547181][]

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

**二、解决方法**

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

![20171122170923769][]

[20171122170537360]: /images/20220605/7eef051ea2124d97b6194c3f66832600.png
[20171122170547181]: /images/20220605/6778321ffa33480db8665e5a8377ab6b.png
[20171122170923769]: /images/20220605/97c4d063f0024ce8ba71c0208d971f02.png