ORACLE 查询某一个月的每天的一条数据，查询一天的每个小时的数据，查询某一年的每月的一条数据求助怎么写SQL？

[竹___]

如题，求助怎么写啊？

[竹___]

查询一天的也是每小时一条数据，写错了，求助

[卖水果的net]

楼主给出一些测试数据，和你想要的结果；

[竹___]

引用 2 楼 wmxcn2000 的回复:

楼主给出一些测试数据，和你想要的结果；

表如图一样 就是每个月有30天，每天取一条数据，一个月就取30条数据。每天和每年都是对应取24条和12条数据

[卖水果的net]

with m as ( select row_number() over(partition by trunc(realdate) order by realdate) rn, t.* from t ) select * from rn = 1 -- 大概这个样子

[竹___]

引用 4 楼 wmxcn2000 的回复:

with m as ( select row_number() over(partition by trunc(realdate) order by realdate) rn, t.* from t ) select * from rn = 1 -- 大概这个样子

没成功，能写详细点吗~~

[卖水果的net]

-- 这个是你的表名称

with m as (
select row_number() over(partition by trunc(realtime) order by realtime) rn, t.* 
from T_DATA_WY t
)
select * from m where rn = 1 

[卖水果的net]

-- 每天的
with m as (
select row_number() over(partition by to_char(realtime,'yyyy-mm-dd') order by realtime) rn, t.* 
from T_DATA_WY t
)
select * from m where rn = 1 

-- 每月的
with m as (
select row_number() over(partition by to_char(realtime,'yyyy-mm') order by realtime) rn, t.* 
from T_DATA_WY t
)
select * from m where rn = 1 

-- 每年的
with m as (
select row_number() over(partition by to_char(realtime,'yyyy') order by realtime) rn, t.* 
from T_DATA_WY t
)
select * from m where rn = 1