ROWNUM 是查询之后产生的编号,比查询滞后,所以如果用大于号,则会一条一条数据全部都过滤掉。
SELECT * FROM torderdetail a WHERE ROWNUM <= 10SELECT * FROM (SELECT a.*, ROWNUM rn FROM torderdetail a) WHERE rn >= 10 AND rn <= 20
Row_number() over()这个分析函数是从9I开始提供的,一般的用途和rownum差不多。
一般写法row_number() over( order by order_date desc) 生成的顺序和rownum的语句一样,效率也一样(对于同样有order by 的rownum语句来说),所以在这种情况下两种用法是一样的。
而对于分组后取最近的10条纪录,则是rownum无法实现的,这时只有row_number可以实现,row_number() over(partition by 分组字段 order by 排序字段)就能实现分组后编号,比如说要取近一个月的每天最后10个订单纪录
SELECT *
FROM (
SELECT a.*,ROW_NUMBER () OVER (PARTITION BY TRUNC (order_date) ORDER BY order_date DESC) rn
FROM torderdetail a)
WHERE rn <= 10
分组top:
如果只想得到每个公司最大年份,最大月份数据。(猜测你的需求)
partition by unitname order by year desc,period desc
partition分组函数year,period desc与year desc,period desc 不一样
如果用year,period desc不行,但是分别year desc,period desc 就搞定了,谢谢。
下面是完整的代码
select *
from (select UNITCODE, unitname, year, period, num1,
row_number() over(partition by UNITNAME order by YEAR desc,period desc) mm
from (select BD_CORP.UNITCODE, bd_corp.unitname, gl_voucher.year, gl_voucher.period, count(*) num1
from gl_voucher, bd_corp
where bd_corp.pk_corp = gl_voucher.pk_corp
and gl_voucher.dr='0'
group by BD_CORP.UNITCODE,
bd_corp.unitname,
gl_voucher.year,
gl_voucher.period)
)
where mm = 1
ORDER BY UNITCODE;