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

来源：互联网发布：php人才招聘系统编辑：程序博客网时间：2024/05/19 17:59

一、问题描述

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

第一页的数据:

SELECT *FROM (SELECT u.*, rownum AS rFROM (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.scoreFROM HEALTHDOCTOR docLEFT 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, docidFROM healthevaluateGROUP BY docid) luate ON doc.id = luate.docid LEFT JOIN (SELECT COUNT(*) AS ordersum, docidFROM HealthyOrderGROUP BY docid) orders ON doc.id = orders.docid WHERE rice.GRAPHICPRICE >= 0) uWHERE rownum < 13)WHERE r >= 1

第二页的数据:

SELECT *FROM (SELECT u.*, rownum AS rFROM (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.scoreFROM HEALTHDOCTOR docLEFT 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, docidFROM healthevaluateGROUP BY docid) luate ON doc.id = luate.docid LEFT JOIN (SELECT COUNT(*) AS ordersum, docidFROM HealthyOrderGROUP BY docid) orders ON doc.id = orders.docid WHERE rice.GRAPHICPRICE >= 0 ) uWHERE rownum < 25)WHERE r >= 13

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

二、解决方法

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

阅读全文

0 0