with t as ( select 1 sendid, 100 sendv, '3:45:00' sendt from dual union all select 1 sendid, 100 sendv, '3:40:00' sendt from dual union all select 2 sendid, 200 sendv, '3:45:00' sendt from dual union all select 2 sendid, 200 sendv, '3:40:00' sendt from dual union all select 3 sendid, 300 sendv, '3:45:50' sendt from dual union all select 3 sendid, 300 sendv, '3:45:40' sendt from dual ) select sendid,sendv, max(sendt) from t group by sendid, sendv不知道楼主到底什么意思,是不是太简单了我理解错了
如果 senid 对应的 sendv 都是相同的,那楼上的方法直接求 sendt的max就可以。如果不同。 select sendid, max(sendt),max(sendv)keep(dense_rank first order by sendt desc ) sendt from t group by sendid
with t as (select 1 sendid, 100 sendv, '3:45:00' sendt from dual union all select 1 sendid, 100 sendv, '3:40:00' sendt from dual union all select 2 sendid, 200 sendv, '3:45:00' sendt from dual union all select 2 sendid, 200 sendv, '3:40:00' sendt from dual union all select 3 sendid, 300 sendv, '3:45:50' sendt from dual union all select 3 sendid, 300 sendv, '3:45:40' sendt from dual) select sendid, sendv, sendt from (select sendid, sendv, sendt, row_number() over(partition by sendid order by sendt desc) as rn from t) where rn = 1;
select t1.senid,t2.senvalue,t2.time from (select senid,max(time)max_time from table group by senid)t1,table t2 where t1.senid=t2.senid and t1.max_time=t2.time
with t as
(
select 1 sendid, 100 sendv, '3:45:00' sendt from dual
union all
select 1 sendid, 100 sendv, '3:40:00' sendt from dual
union all
select 2 sendid, 200 sendv, '3:45:00' sendt from dual
union all
select 2 sendid, 200 sendv, '3:40:00' sendt from dual
union all
select 3 sendid, 300 sendv, '3:45:50' sendt from dual
union all
select 3 sendid, 300 sendv, '3:45:40' sendt from dual
)
select sendid,sendv, max(sendt) from t group by sendid, sendv不知道楼主到底什么意思,是不是太简单了我理解错了
select sendid, max(sendt),max(sendv)keep(dense_rank first order by sendt desc ) sendt from t group by sendid
with t as
(select 1 sendid, 100 sendv, '3:45:00' sendt
from dual
union all
select 1 sendid, 100 sendv, '3:40:00' sendt
from dual
union all
select 2 sendid, 200 sendv, '3:45:00' sendt
from dual
union all
select 2 sendid, 200 sendv, '3:40:00' sendt
from dual
union all
select 3 sendid, 300 sendv, '3:45:50' sendt
from dual
union all
select 3 sendid, 300 sendv, '3:45:40' sendt from dual)
select sendid, sendv, sendt
from (select sendid,
sendv,
sendt,
row_number() over(partition by sendid order by sendt desc) as rn
from t)
where rn = 1;
恩 是的,我的例子有问题,senValue的值是不相同的。
dataId senid senValue time
***** 1 100 3:45:00
***** 1 120 3:40:00
***** 1 130 3:35:00
***** 1 110 3:30:00
***** 2 2500 3:45:00
***** 2 2100 3:44:00
***** 2 2900 3:43:00
***** 2 2200 3:42:00
***** 3 320 3:44:50
***** 3 400 3:44:40
***** 3 500 3:44:30
***** 3 100 3:44:20
我的意思是求出每个senid对应的最新的一条数据。值不一定是最大的。主要是要最新的数据。
where t1.senid=t2.senid and t1.max_time=t2.time