高分求oracle中根据类别查询出每类的前2条,按时间排序,以下为数据库脚本:不能用游标,只能使用纯SQL查询语句。create table test1
(
tId number(10),
tName varchar2(50),
tType number(5),
endTime Date
)
commit;
insert into test1 values(1,'test1',1,to_date('2002-08-26 12:12:12','yyyy-mm-dd HH24:mi:ss'));
insert into test1 values(2,'test2',1,to_date('2002-07-26 10:12:12','yyyy-mm-dd HH24:mi:ss'));
insert into test1 values(3,'test3',1,to_date('2002-06-26 12:12:12','yyyy-mm-dd HH24:mi:ss'));
insert into test1 values(4,'test4',1,to_date('2002-05-26 19:12:12','yyyy-mm-dd HH24:mi:ss'));
insert into test1 values(5,'test5',1,to_date('2002-04-26 17:12:12','yyyy-mm-dd HH24:mi:ss'));insert into test1 values(6,'test6',2,to_date('2004-08-26 12:12:12','yyyy-mm-dd HH24:mi:ss'));
insert into test1 values(7,'test7',2,to_date('2005-07-26 10:12:12','yyyy-mm-dd HH24:mi:ss'));
insert into test1 values(8,'test8',2,to_date('2006-06-26 12:12:12','yyyy-mm-dd HH24:mi:ss'));
insert into test1 values(9,'test9',2,to_date('2007-05-26 19:12:12','yyyy-mm-dd HH24:mi:ss'));
insert into test1 values(10,'test10',2,to_date('2008-04-26 17:12:12','yyyy-mm-dd HH24:mi:ss'));insert into test1 values(11,'test11',3,to_date('2009-08-26 12:12:12','yyyy-mm-dd HH24:mi:ss'));
insert into test1 values(12,'test12',3,to_date('2009-07-26 10:12:12','yyyy-mm-dd HH24:mi:ss'));
insert into test1 values(13,'test13',3,to_date('2009-06-26 12:12:12','yyyy-mm-dd HH24:mi:ss'));
insert into test1 values(14,'test14',3,to_date('2009-05-26 19:12:12','yyyy-mm-dd HH24:mi:ss'));
insert into test1 values(15,'test15',3,to_date('2009-04-26 17:12:12','yyyy-mm-dd HH24:mi:ss'));
commit;
(
tId number(10),
tName varchar2(50),
tType number(5),
endTime Date
)
commit;
insert into test1 values(1,'test1',1,to_date('2002-08-26 12:12:12','yyyy-mm-dd HH24:mi:ss'));
insert into test1 values(2,'test2',1,to_date('2002-07-26 10:12:12','yyyy-mm-dd HH24:mi:ss'));
insert into test1 values(3,'test3',1,to_date('2002-06-26 12:12:12','yyyy-mm-dd HH24:mi:ss'));
insert into test1 values(4,'test4',1,to_date('2002-05-26 19:12:12','yyyy-mm-dd HH24:mi:ss'));
insert into test1 values(5,'test5',1,to_date('2002-04-26 17:12:12','yyyy-mm-dd HH24:mi:ss'));insert into test1 values(6,'test6',2,to_date('2004-08-26 12:12:12','yyyy-mm-dd HH24:mi:ss'));
insert into test1 values(7,'test7',2,to_date('2005-07-26 10:12:12','yyyy-mm-dd HH24:mi:ss'));
insert into test1 values(8,'test8',2,to_date('2006-06-26 12:12:12','yyyy-mm-dd HH24:mi:ss'));
insert into test1 values(9,'test9',2,to_date('2007-05-26 19:12:12','yyyy-mm-dd HH24:mi:ss'));
insert into test1 values(10,'test10',2,to_date('2008-04-26 17:12:12','yyyy-mm-dd HH24:mi:ss'));insert into test1 values(11,'test11',3,to_date('2009-08-26 12:12:12','yyyy-mm-dd HH24:mi:ss'));
insert into test1 values(12,'test12',3,to_date('2009-07-26 10:12:12','yyyy-mm-dd HH24:mi:ss'));
insert into test1 values(13,'test13',3,to_date('2009-06-26 12:12:12','yyyy-mm-dd HH24:mi:ss'));
insert into test1 values(14,'test14',3,to_date('2009-05-26 19:12:12','yyyy-mm-dd HH24:mi:ss'));
insert into test1 values(15,'test15',3,to_date('2009-04-26 17:12:12','yyyy-mm-dd HH24:mi:ss'));
commit;
解决方案 »
- 我想了解数据库对SQL语句如何进行优化的,能在哪找到资料?
- ?????急急急!!!!oracle资源管理器中给不同的用户组分配cpu百分比时和cpu的时间片有关系吗?
- 新手菜鸟问题:如何把ORACLE数据表导出到WORD或者EXCEL中?在线等..
- OracleTNSListener启动失败。
- 动态sql中的字符的问题
- 请教一个Oracle中的触发器语句!
- 字符串分割问题
- linux下oracle建库/表失败??为什么啊??
- oracle 10g无法登陆问题?
- 我想看DBWR0进程的情况,包括改善,在哪里看啊?
- timesten 中怎么查看和设置PermSize啊?
- Oracle查询分组数据中每个分组的前5条记录(用一条sql实现)
--时间降序
select tId,tName,tType,endTime from (
select tId,tName,tType,endTime,row_number()over(partition by tType order by endTime desc) rn from test1
)
where rn<=2;
排序函数楼主好好看下oracle的分析函数,很好用的
(
select t.* , (select count(1) from test1 where tType = t.tType and endTime > t.endTime) + 1 px from test1 t
) m
where px <= 2
row_number()
1 2 3 4 ...
rank()
1 2 2 4 ...
dense_rank()
1 2 2 3 ...
select tId,tName,tType,endTime
from
(select tId,tName,tType,endTime,
row_number() over(partition by tType order by endTime desc) rn
from test1) a
where rn=2
from
(select tId,tName,tType,endTime,
row_number() over(partition by tType order by endTime desc) rn
from test1) a
where rn<=2
select tId,tName,tType,endTime
from
(select tId,tName,tType,endTime,
row_number() over(partition by tType order by endTime desc) rn
from test1) a
where rn<=2