select * from tableName where maxValue in (select max(maxValue),... from tableName order by to_daye(rq,'YYYY-MM'));
select to_date(rq,'YYYY-MM'),maxValue,maxValueTime from tableName where maxValue= (select max(maxValue) from tableName group by to_daye(rq,'YYYY-MM'))
select rq,value from (select rank() over(partition by trunc(rq,'mm') order by value desc) rk,rq,value from tableName) where rk=1
select * from tableName where maxValue in (select max(maxValue) from tableName group by to_daye(rq,'YYYY-MM'));
如果月内没有相同峰值: select a.trunc(a.rq,'mm'), a.maxValue,...... from ( select trunc(rq,'mm')) mmon , max(maxValue) maxv from tableName b group by trunc(rq,'mm')) c, tableName a where trunc(a.rq,'mm') = c.mmon and a.maxvalue = c.maxv
月内有同值,仅取其一:select trunc(rq,'mm'),MaxValue,... from (select row_number() over(partition by trunc(rq,'mm') order by maxvalue desc) rn,rq,maxvalue,.... from tableName) where rn = 1
不好意思,拖了两天 下面是我测试的结果:SQL> select * from test;RQ MAXVALUE MAXVALUETIME ----------- -------- ------------ 2003-6-1 18.08 2003-6-1 下午 01:24:00 2003-6-2 28.08 2003-6-2 上午 10:19:00 2003-7-1 88.08 2003-7-1 下午 01:22:00 2003-7-2 67.08 2003-7-2 上午 10:19:00 2003-8-17 10.08 2003-8-17 上午 12:19:00 2003-8-2 25.08 2003-8-2 上午 03:19:006 rows selectedSQL> select rq,maxvalue,maxvaluetime 2 from test 3 where (trunc(rq,'MM'),maxvalue) in 4 ( select trunc(rq,'MM'),max(maxvalue) 5 from test 6 group by trunc(rq,'MM'));RQ MAXVALUE MAXVALUETIME ----------- -------- ------------ 2003-6-2 28.08 2003-6-2 上午 10:19:00 2003-7-1 88.08 2003-7-1 下午 01:22:00 2003-8-2 25.08 2003-8-2 上午 03:19:00SQL>
不错,要注意一点,oracle中聚合函数和SQL server中的使用情况有很大的区别要使用时,必须在group by 子句中
where maxValue in (select max(maxValue),... from tableName
order by to_daye(rq,'YYYY-MM'));
(select max(maxValue) from tableName
group by to_daye(rq,'YYYY-MM'))
(select rank() over(partition by trunc(rq,'mm') order by value desc) rk,rq,value from tableName)
where rk=1
where maxValue in (select max(maxValue) from tableName
group by to_daye(rq,'YYYY-MM'));
select a.trunc(a.rq,'mm'), a.maxValue,......
from ( select trunc(rq,'mm')) mmon ,
max(maxValue) maxv from tableName b group by trunc(rq,'mm')) c,
tableName a
where trunc(a.rq,'mm') = c.mmon and a.maxvalue = c.maxv
from (select row_number() over(partition by trunc(rq,'mm') order by maxvalue desc) rn,rq,maxvalue,.... from tableName)
where rn = 1
下面是我测试的结果:SQL> select * from test;RQ MAXVALUE MAXVALUETIME
----------- -------- ------------
2003-6-1 18.08 2003-6-1 下午 01:24:00
2003-6-2 28.08 2003-6-2 上午 10:19:00
2003-7-1 88.08 2003-7-1 下午 01:22:00
2003-7-2 67.08 2003-7-2 上午 10:19:00
2003-8-17 10.08 2003-8-17 上午 12:19:00
2003-8-2 25.08 2003-8-2 上午 03:19:006 rows selectedSQL> select rq,maxvalue,maxvaluetime
2 from test
3 where (trunc(rq,'MM'),maxvalue) in
4 ( select trunc(rq,'MM'),max(maxvalue)
5 from test
6 group by trunc(rq,'MM'));RQ MAXVALUE MAXVALUETIME
----------- -------- ------------
2003-6-2 28.08 2003-6-2 上午 10:19:00
2003-7-1 88.08 2003-7-1 下午 01:22:00
2003-8-2 25.08 2003-8-2 上午 03:19:00SQL>