有个数据表 table1字段:fid(int) node_id, temperature(double), time(varchar2) original_time(varchar2)
1 00005 25.3 2011-08-24 00:00 2011-08-24 00:00:01
2 00005 25.6 2011-08-24 00:00 2011-08-24 00:00:02
3 00005 26.7 2011-08-24 00:00 2011-08-24 00:00:03
4 00006 27.8 2011-08-24 01:00 2011-08-24 01:00:03
8 00006 24.3 2011-08-24 01:00 2011-08-24 01:00:04
6 00006 27.8 2011-08-24 01:00 2011-08-24 01:00:05
7 00005 28.9 2011-08-24 02:00 2011-08-24 02:00:01
5 00005 29.5 2011-08-24 02:00 2011-08-24 02:00:02得出下面列表: fid node_id temperature
3 00005 26.7
6 00006 27.8
5 00005 29.5根据original_time 日期时间的最晚记录 获取每组time和node_id记录.
1 00005 25.3 2011-08-24 00:00 2011-08-24 00:00:01
2 00005 25.6 2011-08-24 00:00 2011-08-24 00:00:02
3 00005 26.7 2011-08-24 00:00 2011-08-24 00:00:03
4 00006 27.8 2011-08-24 01:00 2011-08-24 01:00:03
8 00006 24.3 2011-08-24 01:00 2011-08-24 01:00:04
6 00006 27.8 2011-08-24 01:00 2011-08-24 01:00:05
7 00005 28.9 2011-08-24 02:00 2011-08-24 02:00:01
5 00005 29.5 2011-08-24 02:00 2011-08-24 02:00:02得出下面列表: fid node_id temperature
3 00005 26.7
6 00006 27.8
5 00005 29.5根据original_time 日期时间的最晚记录 获取每组time和node_id记录.
解决方案 »
- 如何编写一个循环查询表中记录并把结果保存在临时表中
- 高手们帮忙优化下这个UPDATE 语句
- 如何将 Oracle 数据导出到 Excel 中?
- 在windows 2000 server上能不能安装两个版本的Oracle,比如说是8i和9i?
- 在ORACLE里有没有什么办法把所有表的建表语导出来,还有所的存储过程的语句导出来。
- 求救,我改了机器名(需要改),发现OracleOraHome81Agent服务不能启动,其他服务都能启动,不知怎么回事?
- 如何将表中的500万条记录存成dbf文件
- TOAD的问题,谢谢
- 高分请教关于字段的问题
- 新手上路,求一个触发器的写法
- Mysql中有Date()函数,对应Oracle呢 ?
- 有关Oracle区分单引号和不区分单引号的问题
select min(fid)keep(dense_ranke first order by original_time desc) fid,
min(node_id)keep(dense_ranke first order by original_time desc) node_id,
min(temperature)keep(dense_ranke first order by original_time desc) temperature
from table1;
--没注意到你还要求分组
select min(fid)keep(dense_ranke first order by original_time desc) fid,
min(node_id)keep(dense_ranke first order by original_time desc) node_id,
min(temperature)keep(dense_ranke first order by original_time desc) temperature
from table1
group by time,node_id;
select fid,node_id,temperature from
(
select fid,node_id,temperature,
row_number() over(partition by time,node_id order by original_time DESC) rn
from table1
where rn < 2
)
(
select fid,node_id,temperature,
row_number() over(partition by time,node_id order by original_time DESC) rn
from table1
)aa
where aa.rn < 2
where语句里的rn,不可以直接这么用吧。
select min(fid)keep(dense_rank first order by original_time desc) fid,
min(node_id)keep(dense_rank first order by original_time desc) node_id,
min(temperature)keep(dense_rank first order by original_time desc) temperature
from table1
group by time,node_id;
dense_rank 是干什么用的?
--说了多写一个E了
select min(fid)keep(dense_rank first order by original_time desc) fid,
min(node_id)keep(dense_rank first order by original_time desc) node_id,
min(temperature)keep(dense_rank first order by original_time desc) temperature
from table1
group by time,node_id;