Oracle查询前100万条数据

最新推荐文章于 2024-08-23 04:19:53 发布

ZHOU_VIP

最新推荐文章于 2024-08-23 04:19:53 发布

阅读量9.6k

点赞数 1

分类专栏： Orale MySQL

本文链接：https://blog.csdn.net/zhou_vip/article/details/79818174

版权

Orale MySQL 专栏收录该内容

73 篇文章 3 订阅

订阅专栏

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