select *
from
( select field1,field2,...,dense_rank() over (partition by field1 order by field2) order_no from tables)
where order_no <= n;
from
( select field1,field2,...,dense_rank() over (partition by field1 order by field2) order_no from tables)
where order_no <= n;
其中,field1为分组字段;
比如顾客销售记录表有字段 销售网点,顾客名,消费金额。如要统计每个销售网点的消费金额最多的100位顾客:
select * from (
select 销售网点,顾客名,消费金额,dense_rank() over (partition by 销售网点 order by 消费金额 desc) order_no from 顾客销售记录表
where order_no <= 100;
注意:
根据不同的需求,dense_rank() 可以用
row_number(
)、
rank( )、
ntile( )(SQL Server)替代。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/83911/viewspace-702378/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/83911/viewspace-702378/