Oracle 的 Rownum 是一個 Pseudocolumn (虛列)
意思是不存在的 Column (列),是經由計算得來的
Rownum能被廣泛使用,來推舉幾個例子唄!
例 1. 發展組欲取得產品型錄資料庫中的前1000筆資料
select *
from tb_catalog
where Rownum <= 1000
//只要在 where 條件,加上 Rownum <=1000 即可取得資料庫前1000筆資料
例 2. 發展組欲取得產品型錄資料庫中,依型錄建立日期來升冪排序,且是第2000筆到第3000筆資料
select * from (
select catalog.*, rownum row_num from (
select *
from tb_catalog
order by creation_time
) catalog where rownum <= 3000
) where row_num > 2000
//這得分三層來作業了!!
//第一層,先用 order by creation_time 將型錄建立日期升冪排序
select *
from tb_catalog
order by creation_time
//第二層,包住已排序的資料後加上 rownum <= 3000,取得排序後的前3000筆資料
select catalog.*, rownum row_num from (
select *
from tb_catalog
order by creation_time )
catalog where rownum <= 3000
// 第三層,最大包!將已排序且前3000筆資料,再加上 row_num > 2000
select * from (
select catalog.*, rownum row_num from (
select *
from tb_catalog
order by creation_time)
catalog where rownum <= 3000
) where row_num > 2000
例 3. 發展組為了推廣ET會員,請協助跑所有EC會員資料!
EC會員資料統計結果18萬筆資料
但!因為 Excel 2003 行數限制僅能 65536,若龐大資料庫18萬筆以上怎取出xls?
select * from (
select catalog.*, rownum row_num from (
select *
from tb_catalog
order by creation_time)
catalog where rownum <= 60000
) where row_num > 1
select * from (
select catalog.*, rownum row_num from (
select *
from tb_catalog
order by creation_time)
catalog where rownum <= 120000
) where row_num > 60000
//同例(2)方法,只要將 rownum 數值改變,即可6萬、6萬筆資料匯出產生多個xls
I am grateful to acquire found this site. Keep up the excellent postings.