有个数据表 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记录.
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;