有一个表 a ,有如下数据id type time
1 a 20070605
2 b 20070606
3 b 20070530
4 a 20070610
5 c 20070609
6 a 20070608
7 b 20070601
8 a 20070602
9 c 20070612
10 c 20070623请问如何获得的每个 type 按时间desc排序的前2项啊??是每个 type 都返回2项.就是返回结果如下:id type time
4 a 20070610
6 a 20070608
2 b 20070606
7 b 20070601
10 c 20070623
9 c 20070612在线等.
1 a 20070605
2 b 20070606
3 b 20070530
4 a 20070610
5 c 20070609
6 a 20070608
7 b 20070601
8 a 20070602
9 c 20070612
10 c 20070623请问如何获得的每个 type 按时间desc排序的前2项啊??是每个 type 都返回2项.就是返回结果如下:id type time
4 a 20070610
6 a 20070608
2 b 20070606
7 b 20070601
10 c 20070623
9 c 20070612在线等.
from (select t.*,
count(*) over(partition by type order by time desc) as cnt
from a t) tt
where tt.cnt <= 2
select 1,'a','20070605' from dual
union all
select 2,'b','20070606' from dual
union all
select 3,'b','20070530' from dual
union all
select 4,'a','20070610' from dual
union all
select 5,'c','20070609' from dual
union all
select 6,'a','20070608' from dual
union all
select 7,'b','20070601' from dual
union all
select 8,'a','20070602' from dual
union all
select 9,'c','20070612' from dual
union all
select 10,'c','20070623' from dual
--执行sql
select id,type,time from
(select id,type,time,row_number()over(partition by type order by time desc) inx from test2) t
where t.inx<=2
--Result
1 4 a 20070610
2 6 a 20070608
3 2 b 20070606
4 7 b 20070601
5 10 c 20070623
6 9 c 20070612
小弟笨拙,在学习数据库的过程中都没有接触到 over
请指教.
row_number()over(partition by type order by time desc) inx 这个是怎么什么意思啊?
小弟笨拙,在学习数据库的过程中都没有接触到 over
请指教.
分析函数
具体的在Google上G一下