SELECT TIME, v
FROM Table1
WHERE (To_Char(TIME, 'yyyy-mm'), v) IN
(SELECT To_Char(TIME, 'yyyy-mm'), MAX(v)
FROM Table1
WHERE To_Char(TIME, 'mm') = '03'
GROUP BY To_Char(TIME, 'yyyy-mm'))
;
FROM Table1
WHERE (To_Char(TIME, 'yyyy-mm'), v) IN
(SELECT To_Char(TIME, 'yyyy-mm'), MAX(v)
FROM Table1
WHERE To_Char(TIME, 'mm') = '03'
GROUP BY To_Char(TIME, 'yyyy-mm'))
;
FROM Table1
WHERE (To_Char(TIME, 'yyyy-mm'), v) IN
(SELECT To_Char(TIME, 'yyyy-mm'), MAX(v)
FROM Table1
WHERE To_Char(TIME, 'mm') = '03'
GROUP BY To_Char(TIME, 'yyyy-mm'))
AND To_Char(TIME, 'yyyy') >=
(SELECT To_Number(MAX(To_Char(TIME, 'yyyy'))) - 10 FROM Table1)
;
select time,v from
(
select time,v,dense_rank() over(partition by trunc(time,'MM') order by v desc) rank_v from table1 where to_char(time,'MM') = '03'
)
where rank_v =1
2.
select time,v from
(
select time,v,dense_rank() over(order by trunc(time,'YYYY') desc) rank_time,dense_rank() over(partition by trunc(time,'MM') order by v desc) rank_v from table1 where to_char(time,'MM') = '03'
)
where rank_time <=10 and rank_v =1
(
select t.*,row_number() over(partition by to_char(time,'MM') order by v desc) rownum1 from table1 t where to_char(time,'MM') = '03'
)
where rownum1 =1
2.
select * from
(
select t.*,row_number() over(order by to_char(time,'YYYY') desc) rownum1,row_number() over(partition by to_char(time,'MM') order by v desc) rownum2 from table1 t where to_char(time,'MM') = '03'
)
where rownum1<=10 and rownum2 =1
而对于row_number() over(partition by to_char(time,'MM') order by v desc),如果最大的记录对应的日期有2天的话,这row——number()只能取到其中一天