sql取出每月最早的上报的数据

oracle:

每月最新一条：

SELECT * FROM (select data_time,meter_id,TT_CREDIT,RE_CREDIT,DATA_STR,min(data_time) over(partition by TO_Char(data_time,'YYYY-MM')) as "atime" from WATER_DATA where 1=1
and meter_id like '%111111111111%'
) x where data_time="atime"

每月最后一条：

SELECT * FROM (select data_time,meter_id,TT_CREDIT,RE_CREDIT,DATA_STR,max(data_time) over(partition by TO_Char(data_time,'YYYY-MM')) as "atime" from WATER_DATA where 1=1
and meter_id like '%111111111111%'
) x where data_time="atime"

sql:

select FD3d0,FCreateDate,FM3m0
from (select FD3d0,FCreateDate,FM3m0 ,row_number() over (partition by FD3d0 order by FCreateDate) as rn
from TUpdateself where FCreateDate between '" + fromdate+"' and DATEADD(DAY,1,'"+fromdate+"')) t where rn<=1

参考：https://blog.csdn.net/qq_33556442/article/details/89174484

--取出当月最小值
select * from TUpdateself A
where CONVERT(varchar, FCreateDate, 23) >='2021-05-01' and CONVERT(varchar, FCreateDate, 23) <='2021-05-31'
and FCreateDate<=(
select MIN(FCreateDate) from
(
select * from TUpdateself
where CONVERT(varchar, FCreateDate, 23) >='2021-05-01' and CONVERT(varchar, FCreateDate, 23) <='2021-05-31'
) B where A.FD3d0=B.FD3d0
)

--取出当月最大值
select * from TUpdateself A
where CONVERT(varchar, FCreateDate, 23) >='2021-05-01' and CONVERT(varchar, FCreateDate, 23) <='2021-05-31'
and FCreateDate=(
select max(FCreateDate) from
(
select * from TUpdateself
where CONVERT(varchar, FCreateDate, 23) >='2021-05-01' and CONVERT(varchar, FCreateDate, 23) <='2021-05-31'
) B where A.FD3d0=B.FD3d0
)

--组合 最大值减去最小值得到使用量
select ma.FD3d0,ma.FM3m0, ma.FCreateDate,Mi.FD3d0,Mi.FM3m0, Mi.FCreateDate,(ma.FM3m0-Mi.FM3m0) as cha
from
(
select * from TUpdateself A
where CONVERT(varchar, FCreateDate, 23) >='2021-05-01' and CONVERT(varchar, FCreateDate, 23) <='2021-05-31'
and FCreateDate<=(
select MIN(FCreateDate) from
(
select * from TUpdateself
where CONVERT(varchar, FCreateDate, 23) >='2021-05-01' and CONVERT(varchar, FCreateDate, 23) <='2021-05-31'
) B where A.FD3d0=B.FD3d0
)) Mi
inner join
(
select * from TUpdateself A
where CONVERT(varchar, FCreateDate, 23) >='2021-05-01' and CONVERT(varchar, FCreateDate, 23) <='2021-05-31'
and FCreateDate<=(
select max(FCreateDate) from
(
select * from TUpdateself
where CONVERT(varchar, FCreateDate, 23) >='2021-05-01' and CONVERT(varchar, FCreateDate, 23) <='2021-05-31'
) B where A.FD3d0=B.FD3d0
)) Ma on Mi.FD3d0= Ma.FD3d0