用户名:   自动登录 找回密码
密   码:    * 注册




发表新帖 回复这个主题  [ 1 篇帖子 ] 
作者 内容
 文章标题 : row_number() over partition 实例
帖子发表于 : 2012-03-29 9:05 
离线
头像

注册: 2011-05-01 9:15
帖子: 120
SQL> alter session set nls_date_format='yyyy-mm-dd';

会话已更改。

SQL> select * from awen.row_number where id=12;

ID NAME SAL CYDATE
---------- -------------------- ---------- ----------
12 a 2600 2012-01-11
12 a 2700 2012-01-12
12 a 2800 2012-01-13
12 a 2900 2012-01-14
12 a 3000 2012-01-15
12 b 3100 2012-01-16
12 b 3200 2012-01-17
12 b 3300 2012-01-18
12 b 3400 2012-01-19
12 b 3500 2012-01-20

已选择10行。

想按name字段得到各自按时间倒序的top 3

1、union
SQL> ed
已写入 file afiedt.buf

1 (select id, name, sal, cydate
2 from (select id, name, sal, cydate, rownum rs, rn
3 from (select id, name, sal, cydate, rownum rn
4 from awen.row_number
5 where id = 12
6 and name = 'a'
7 order by cydate desc))
8 where rs < 4) union
9 (select id, name, sal, cydate
10 from (select id, name, sal, cydate, rownum rs, rn
11 from (select id, name, sal, cydate, rownum rn
12 from awen.row_number
13 where id = 12
14 and name = 'b'
15 order by cydate desc))
16* where rs < 4)
SQL> /

ID NAME SAL CYDATE
---------- -------------------- ---------- ----------
12 a 2800 2012-01-13
12 a 2900 2012-01-14
12 a 3000 2012-01-15
12 b 3300 2012-01-18
12 b 3400 2012-01-19
12 b 3500 2012-01-20

已选择6行。

2、row_number() over partition

SQL> ed
已写入 file afiedt.buf

1 select id, name, sal, cydate
2 from (select id,name,sal,cydate,row_number() over(partition by name order b
y cydate desc) rs
3 from awen.row_number where id = 12)
4* where rs < 4
SQL> /

ID NAME SAL CYDATE
---------- -------------------- ---------- ----------
12 a 3000 2012-01-15
12 a 2900 2012-01-14
12 a 2800 2012-01-13
12 b 3500 2012-01-20
12 b 3400 2012-01-19
12 b 3300 2012-01-18

已选择6行。


页首
 用户资料  
 
显示帖子 :  排序  
发表新帖 回复这个主题  [ 1 篇帖子 ] 


在线用户

注册用户: 没有注册用户


查找:
前往 :  
cron
Powered by OraSql © 2011, 2012, oracle_awen