select min(time),a from (select time,max(v) a from table1 where month(time)='3' group by time)group by a 注: month() 是SQL SERVER 2000中的从日期中取得月份的函数。在ORACLE 中对应函数自己去查吧。
select min(time),max(v) from ( select * from (select * from table where to_char(time,'mm')='03') where v=(select max(v) from table where to_char(time,'mm')='03') ) group by trunc(time,'mm')第一步:select * from table where to_char(time,'mm')='03' 选出月份为3的time行 第二步:...where v=select max(v).... 选出各年3月份中V最大的行 第三步:group by trunc(time,'mm') 按年的月份分组,取分组中的最小time和最大v(事实上v已经最大了,为了配合group by 写了一个聚合函数)
select min(time),a from (select time,max(v) a from table1 where to_char(time,'MM')='3' group by time)group by a
select min(time),a from (select time,max(v) a from table1 where to_char(time,'MM')='03' group by trunc(time,'YYYY-MM')) group by a
to chenbing21(icepoint): 不行,最后一个SQL语句有误,其他语句照样出现重复!to SevenChina(秋枝): 查询结果只有一个记录,最大v值与最小time.
select time, v from table1 where to_char(time,'mm')='03' and v = (select max(v) from table1 where to_char(time,'mm')='03')
select min(time), max(v) from (select time, v from table1 where to_char(time,'mm')='03' and v = (select max(v) from table1 where to_char(time,'mm')='03') )
SQL> select * from t1;TIME V ---------- ---------- 1999-04-01 3 1999-03-01 3 1999-03-10 4 1999-03-12 4 2000-04-12 3 2000-03-01 3 2000-03-12 4 2000-03-10 48 rows selected.SQL> select time,v from t1, 2 ( 3 select min(t1.rowid) mr from t1, 4 ( 5 select to_char(time,'yyyymm') t,max(v) mv from t1 6 where to_char(time,'mm')=3 7 group by to_char(time,'yyyymm') 8 ) t2 9 where to_char(t1.time,'yyyymm')=t2.t and t1.v=t2.mv 10 group by mv,t 11 ) t3 12 where t1.rowid=mr 13 /TIME V ---------- ---------- 1999-03-10 4 2000-03-12 4SQL>
select * from ( select t.*,row_number() over(partition by to_char(time,'yyyy') order by v desc,time) rownum1 from table1 t where to_char(time,'mm') = '03' ) where rownum1 = 1 这个没问题的,我式过了
to: ORARichard(没钱的日子......) 你选出来的2000年的time应是2000-3-10才付合要求啊!
1. select time,v from ( select time,v,row_number() over(partition by trunc(time,'MM') order by v desc,time 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,row_number() over(partition by trunc(time,'MM') order by v desc,time desc) rank_v from table1 where to_char(time,'MM') = '03' ) where rank_time <=10 and rank_v =1
time)group by a 注: month() 是SQL SERVER 2000中的从日期中取得月份的函数。在ORACLE 中对应函数自己去查吧。
(
select * from (select * from table where to_char(time,'mm')='03')
where v=(select max(v) from table where to_char(time,'mm')='03')
)
group by trunc(time,'mm')第一步:select * from table where to_char(time,'mm')='03' 选出月份为3的time行
第二步:...where v=select max(v).... 选出各年3月份中V最大的行
第三步:group by trunc(time,'mm') 按年的月份分组,取分组中的最小time和最大v(事实上v已经最大了,为了配合group by 写了一个聚合函数)
time)group by a
group by trunc(time,'YYYY-MM')) group by a
不行,最后一个SQL语句有误,其他语句照样出现重复!to SevenChina(秋枝):
查询结果只有一个记录,最大v值与最小time.
time v
1999-4-1 3
1999-3-1 3
1999-3-10 4
1999-3-12 4
2000-4-12 3
2000-3-1 3
2000-3-12 4
2000-3-10 4
执行结果为:
1999-3-10 4
2000-3-10 4
where to_char(time,'mm')='03'
and v = (select max(v) from table1 where to_char(time,'mm')='03')
from (select time, v from table1
where to_char(time,'mm')='03'
and
v = (select max(v) from table1 where to_char(time,'mm')='03')
)
---------- ----------
1999-04-01 3
1999-03-01 3
1999-03-10 4
1999-03-12 4
2000-04-12 3
2000-03-01 3
2000-03-12 4
2000-03-10 48 rows selected.SQL> select time,v from t1,
2 (
3 select min(t1.rowid) mr from t1,
4 (
5 select to_char(time,'yyyymm') t,max(v) mv from t1
6 where to_char(time,'mm')=3
7 group by to_char(time,'yyyymm')
8 ) t2
9 where to_char(t1.time,'yyyymm')=t2.t and t1.v=t2.mv
10 group by mv,t
11 ) t3
12 where t1.rowid=mr
13 /TIME V
---------- ----------
1999-03-10 4
2000-03-12 4SQL>
(
select t.*,row_number() over(partition by to_char(time,'yyyy') order by v desc,time) rownum1
from table1 t where to_char(time,'mm') = '03'
)
where rownum1 = 1
这个没问题的,我式过了
你选出来的2000年的time应是2000-3-10才付合要求啊!
select time,v from
(
select time,v,row_number() over(partition by trunc(time,'MM') order by v desc,time 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,row_number() over(partition by trunc(time,'MM') order by v desc,time desc) rank_v from table1 where to_char(time,'MM') = '03'
)
where rank_time <=10 and rank_v =1