Oracle 分页数据重复

查询语句: 查询 original_ip 为 172.18.129.251 的数据,没有重复的数据

SELECT *
  FROM TB_IP_PORT_TRANSFER t
 WHERE t.original_ip = '172.18.129.251'
 ORDER BY time DESC;

分页查询语句: 

SELECT *
  FROM (SELECT TMP.*, ROWNUM ROW_ID
          FROM (SELECT time,
                       time_val,
                       original_ip,
                       orginal_ip_val,
                       original_port,
                       nat_ip
                       nat_ip_val,
                       nat_port
                  FROM TB_IP_PORT_TRANSFER
                 ORDER BY time DESC desc) TMP
         WHERE ROWNUM <= ?)
 WHERE ROW_ID > ?;

第一页数据【10(Long), 0(Long)】和第二页数据【20(Long), 10(Long)】有重复,结果如下

修改后的分页语句查询:

SELECT *
  FROM (SELECT TMP.*, ROWNUM ROW_ID
          FROM (SELECT time,
                       time_val,
                       original_ip,
                       orginal_ip_val,
                       original_port,
                       nat_ip,
                       nat_ip_val,
                       nat_port
                  FROM TB_IP_PORT_TRANSFER
                 ORDER BY time_val DESC) TMP)
 WHERE ROW_ID <= ?
   AND ROW_ID > ?

存在的问题:

修改后的语句,在数据量多的时候,查询速度慢。

产生重复数据的原因,后续学习再进行补偿。

寄语:

期待大神指正高效方法。

 

 

相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页