Oracle查询前100万条数据

最新推荐文章于 2025-06-23 16:10:06 发布

原创最新推荐文章于 2025-06-23 16:10:06 发布 · 9.8k 阅读

2 ·

CC 4.0 BY-SA版权

Orale MySQL 专栏收录该内容

73 篇文章

订阅专栏

本文介绍在Oracle数据库中实现类似SQL Server的SELECT TOP N查询的方法，通过使用ROWNUM结合ORDER BY来高效地获取指定数量的数据记录。以具体的案例说明如何进行大数据量的截取操作。

Oracle不支持select top语句，在Oracle中经常是用order by跟rownum

select 列名1 ...列名n from
(
select 列名1 ...列名n
from 表名 order by 列名1
)
where rownum <=N(抽出记录数)

order by rownum asc

如：按姓名排序取出前十条数据

select id,name from (select id,name from student order by name) where rownum<=10 order by rownum asc

查询车辆表前100万条数据（按照id排序，主键id是Number类型）：

select ID,
       CASEID,
       PARTYID,
       VEHICLENO,
       TAGCOLOR,
       TCERTIFICATIONCODE,
       RTCISSUER,
       ROUTINENO,
       VEHICLEBRAND,
       VEHICLETYPEID,
       OWNERUNIT,
       OWNERUNITTYPEID,
       DRIVINGSTATEID,
       INSURANCECOMPANYID,
       UPDATETIME,
       UPDATEACCOUNTID,
       TAGEXCEPTIONTYPEID,
       VEHICLEINDENTIFYID,
       REGISTERTIME,
       INSURANCENO,
       WEIGHT,
       APPLYHEIGHT,
       APPLYWIDTH,
       APPLYLENGTH,
       BASICCODEID
  from (select ID,
               CASEID,
               PARTYID,
               VEHICLENO,
               TAGCOLOR,
               TCERTIFICATIONCODE,
               RTCISSUER,
               ROUTINENO,
               VEHICLEBRAND,
               VEHICLETYPEID,
               OWNERUNIT,
               OWNERUNITTYPEID,
               DRIVINGSTATEID,
               INSURANCECOMPANYID,
               UPDATETIME,
               UPDATEACCOUNTID,
               TAGEXCEPTIONTYPEID,
               VEHICLEINDENTIFYID,
               REGISTERTIME,
               INSURANCENO,
               WEIGHT,
               APPLYHEIGHT,
               APPLYWIDTH,
               APPLYLENGTH,
               BASICCODEID
          from TC_VEHICLE
         order by id)
 where rownum <= 1000000
 order by id asc