oracle之分析函数 partition by

--创建临时表 temp
with temp as(
select '设备1' name, to_date('2020-01-01', 'yyyy-mm-dd') time, 26 value from dual union all
select '设备1' name, to_date('2020-01-02', 'yyyy-mm-dd') time, 27 value from dual union all
select '设备1' name, to_date('2020-01-03', 'yyyy-mm-dd') time, 28 value from dual union all

select '设备2' name, to_date('2020-01-01', 'yyyy-mm-dd') time, 29 value from dual union all
select '设备2' name, to_date('2020-01-02', 'yyyy-mm-dd') time, 30 value from dual union all
select '设备2' name, to_date('2020-01-03', 'yyyy-mm-dd') time, 31 value from dual union all

select '设备3' name, to_date('2020-01-01', 'yyyy-mm-dd') time, 32 value from dual union all
select '设备3' name, to_date('2020-01-02', 'yyyy-mm-dd') time, 33 value from dual union all
select '设备3' name, to_date('2020-01-03', 'yyyy-mm-dd') time, 34 value from dual
)

NAME	TIME	VALUE
设备1	2020/1/1	26
设备1	2020/1/2	27
设备1	2020/1/3	28
设备2	2020/1/1	29
设备2	2020/1/2	30
设备2	2020/1/3	31
设备3	2020/1/1	32
设备3	2020/1/2	33
设备3	2020/1/3	34

 

 

 

 

 

 

 

 

 

 

 

--使用分析函数 partition by
--获取所有设备的最新一条数据
select name, value, time from (
    select name, value, time,
    row_number() over(partition by name order by time desc) rn
    from temp
) where rn = 1

NAME	VALUE	TIME
设备1	28	2020/1/3
设备2	31	2020/1/3
设备3	34	2020/1/3