Related to Oracle SQL 关于选择前N行数据的SQL语句

 

数据如下:

 

   COL1   COL2

   -------  -------

  row1  1000
  row2  2000
  row3  2000
  row4  2500
  row4  2600
  row6  2600
  row7  2600

1.  使用ROWNUM，例如选择一张表数据的前5行：SELECT COL1, COL2 FROM TABLE_TEMP WHERE ROWNUM < 6 --语句1

   这种方法需要注意，当语句末尾使用了ORDER BY 时，有可能不会得到你想要的结果，因为ORDER BY 排序实际上是是对“语句1” 选择出的5条结果进行了排序，并不是对全表排序后再返回前5行数据。如果希望对全表排序再返回前N行数据则需要建立一个子查询，如下： 
   SELECT COL1, COL2 FROM
      (SELECT COL1, COL2 FROM TABLE_TEMP ORDER BY COL1 DESC) --对全部数据排序
   WHERE ROWNUM < 6;

结果(共5条数据):

    COL1   COL2

    ----- ----- 

    row7  2600
    row6  2600
    row4  2600
    row4  2500
    row3  2000

 

2. 使用 RANK()和DENSE_RANK () ，这两个需要配合OVER()窗口函数进行使用。RANK()和DENSE_RANK()用来提供顺序号。例如：

       SELECT COL1, COL2, TABLE_RANK FROM
        ( SELECT 

               COL1, COL2,

               RANK() OVER(ORDER BY COL2 NULLS LAST) AS TABLE_RANK --查询结果按COL2字段排序,并且将NULL值排在最后        

          FROM TABLE_TEMP 
          ORDER BY COL2 NULLS LAST

        ) --子查询使用RANK() OVER()选择出TABLE_RANK字段,
   WHERE TABLE_RANK < 6  --选择的数据RANK值范围

 

结果(共7条数据):

   COL1  COL2 TABLE_RANK

   ----- ---- ----------

   row1  1000  1
   row2  2000  2
   row3  2000  2
   row4  2500  4
   row4  2600  5
   row6  2600  5
   row7  2600  5

 

将上边语句中的RANK()替换为DENSE_RANK() 后结果如下：

   COL1  COL2 TABLE_RANK

   ----- ---- ----------

   row1  1000  1
   row2  2000  2
   row3  2000  2
   row4  2500  3
   row4  2600  4
   row6  2600  4
   row7  2600  4

 

 

3. 另外使用RANK()与DENSE_RANK()可以方便的对数据分组并在分组内排序 这需要了解一下OVER(PARTITION BY [PARAMS,PARMMS2] ORDER BY [PARAM3,PARAM4])

   SELECT COL1, COL2, TABLE_RANK FROM
        ( SELECT  COL1, COL2,

               RANK() OVER(PARTITION BY COL1 ORDER BY COL2 NULLS LAST) AS TABLE_RANK --查询结果按COL1字段进行分组，然后每个分组内按COL2字段排序,并且将NULL值排在最后

          FROM TABLE_TEMP 
          ORDER BY COL2 NULLS LAST

        ) --子查询使用RANK() OVER()选择出TABLE_RANK字段,
   WHERE TABLE_RANK < 6  --选择的数据RANK值范围

 

结果如下，注意标红的数据行：

   COL1  COL2 TABLE_RANK

  -----  ---- ----------

   row1  1000  1
   row2  2000  1
   row3  2000  1
   row4  2500  1
   row4  2600  2
   row6  2600  1
   row7  2600  1

 

 

建表SQL:

create table TABLE_TEMP
(
  COL1 NVARCHAR2(40),
  COL2 NVARCHAR2(40)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

 

导入数据SQL:

prompt PL/SQL Developer import file
prompt Created on 2010年12月1日 by Arvin
set feedback off
set define off
prompt Disabling triggers for TABLE_TEMP...
alter table TABLE_TEMP disable all triggers;
prompt Deleting TABLE_TEMP...
delete from TABLE_TEMP;
commit;
prompt Loading TABLE_TEMP...
insert into TABLE_TEMP (COL1, COL2)
values ('row1', '1000');
insert into TABLE_TEMP (COL1, COL2)
values ('row2', '2000');
insert into TABLE_TEMP (COL1, COL2)
values ('row3', '2000');
insert into TABLE_TEMP (COL1, COL2)
values ('row4', '2500');
insert into TABLE_TEMP (COL1, COL2)
values ('row4', '2600');
insert into TABLE_TEMP (COL1, COL2)
values ('row6', '2600');
insert into TABLE_TEMP (COL1, COL2)
values ('row7', '2600');
commit;
prompt 7 records loaded
prompt Enabling triggers for TABLE_TEMP...
alter table TABLE_TEMP enable all triggers;
set feedback on
set define on
prompt Done.