这个SQL怎么写啊?
有一个表 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
在线等.
[解决办法]
select *
from (select t.*,
count(*) over(partition by type order by time desc) as cnt
from a t) tt
where tt.cnt <= 2
[解决办法]
create table test2(id int, type varchar2(10), time varchar2(100));
insert into test2
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
14a20070610
26a20070608
32b20070606
47b20070601
510c20070623
69c20070612
