查了一下帮助,应该是Oracle Analysis in Data Warehouses里的函数。ROW_NUMBER The ROW_NUMBER function assigns a unique number (sequentially, starting from 1, as defined by ORDER BY) to each row within the partition. It has the following syntax:ROW_NUMBER ( ) OVER ( [query_partition_clause] order_by_clause )ROW_NUMBER Example SELECT channel_desc, calendar_month_desc, TO_CHAR(TRUNC(SUM(amount_sold), -6), '9,999,999,999') SALES$, ROW_NUMBER() OVER (ORDER BY TRUNC(SUM(amount_sold), -6) DESC) AS ROW_NUMBER FROM sales, products, customers, times, channels WHERE sales.prod_id=products.prod_id AND sales.cust_id=customers.cust_id AND sales.time_id=times.time_id AND sales.channel_id=channels.channel_id AND times.calendar_month_desc IN ('2000-09', '2000-10') GROUP BY channel_desc, calendar_month_desc;CHANNEL_DESC CALENDAR SALES$ ROW_NUMBER -------------------- -------- -------------- ---------- Direct Sales 2000-10 10,000,000 1 Direct Sales 2000-09 9,000,000 2 Internet 2000-09 6,000,000 3 Internet 2000-10 6,000,000 4 Catalog 2000-09 3,000,000 5 Catalog 2000-10 3,000,000 6 Partners 2000-09 2,000,000 7 Partners 2000-10 2,000,000 8 Tele Sales 2000-09 1,000,000 9 Tele Sales 2000-10 1,000,000 10
9i中分析函数用的,在这里是按照roleID排序
Oracle8i开始支持的分析函数 取得排序后的序号 select roleID,name,descr,status,category from ( select roleID,name,descr,status,category,row_number() over(order by roleID ) num from ROLE ) where num between 1 and 10 查询按roleID排序后的前十条纪录
select roleID,name,descr,status,category from ( select roleID,name,descr,status,category,row_number() over(order by roleID ) num from ROLE ) where num between 1 and 10 意思就是按roleID排序取表Role的前十条记录
The ROW_NUMBER function assigns a unique number (sequentially, starting from 1, as defined by ORDER BY) to each row within the partition. It has the following syntax:ROW_NUMBER ( ) OVER ( [query_partition_clause] order_by_clause )ROW_NUMBER Example
SELECT channel_desc, calendar_month_desc,
TO_CHAR(TRUNC(SUM(amount_sold), -6), '9,999,999,999') SALES$,
ROW_NUMBER() OVER (ORDER BY TRUNC(SUM(amount_sold), -6) DESC)
AS ROW_NUMBER
FROM sales, products, customers, times, channels
WHERE sales.prod_id=products.prod_id AND
sales.cust_id=customers.cust_id AND
sales.time_id=times.time_id AND
sales.channel_id=channels.channel_id AND
times.calendar_month_desc IN ('2000-09', '2000-10')
GROUP BY channel_desc, calendar_month_desc;CHANNEL_DESC CALENDAR SALES$ ROW_NUMBER
-------------------- -------- -------------- ----------
Direct Sales 2000-10 10,000,000 1
Direct Sales 2000-09 9,000,000 2
Internet 2000-09 6,000,000 3
Internet 2000-10 6,000,000 4
Catalog 2000-09 3,000,000 5
Catalog 2000-10 3,000,000 6
Partners 2000-09 2,000,000 7
Partners 2000-10 2,000,000 8
Tele Sales 2000-09 1,000,000 9
Tele Sales 2000-10 1,000,000 10
取得排序后的序号
select roleID,name,descr,status,category from ( select roleID,name,descr,status,category,row_number() over(order by roleID ) num from ROLE ) where num between 1 and 10
查询按roleID排序后的前十条纪录
意思就是按roleID排序取表Role的前十条记录
libin_ftsafe(子陌红尘) 的句子我没有看懂,能不能详细解释一下啊?谢谢了
http://61.144.28.248/dev/bbs_content.jsp?bbs_sn=7101&bbs_page_no=42&bbs_id=0060