现有表A结构及数据如下
S1 S2 S3 S4 S5a点 2009-01-01 11:00:00 0.1 0.5 0b点 2009-02-02 09:00:00 0.2 0.2 1c点 2009-03-03 08:00:00 0.2 0.7 0.5a点 2009-04-04 13:00:00 0.8 0.4 0.9b点 2009-05-05 09:00:00 0.3 0.6 0.7要求查询出S1每个点的max(S3),max(S4),max(S5),并获得产生这些max值得相应时间即所得结果应为:
a点 0.8 2009-04-04 13:00:00 0.5 2009-01-01 11:00:00 0.9 2009-04-04 13:00:00 b点 0.3 2009-02-02 09:00:00 0.6 2009-05-05 09:00:00 1 2009-02-02 09:00:00c点 0.2 2009-03-03 08:00:00 0.7 2009-03-03 08:00:00 0.5 2009-03-03 08:00:00要如何在一条查询中实现?谢谢
S1 S2 S3 S4 S5a点 2009-01-01 11:00:00 0.1 0.5 0b点 2009-02-02 09:00:00 0.2 0.2 1c点 2009-03-03 08:00:00 0.2 0.7 0.5a点 2009-04-04 13:00:00 0.8 0.4 0.9b点 2009-05-05 09:00:00 0.3 0.6 0.7要求查询出S1每个点的max(S3),max(S4),max(S5),并获得产生这些max值得相应时间即所得结果应为:
a点 0.8 2009-04-04 13:00:00 0.5 2009-01-01 11:00:00 0.9 2009-04-04 13:00:00 b点 0.3 2009-02-02 09:00:00 0.6 2009-05-05 09:00:00 1 2009-02-02 09:00:00c点 0.2 2009-03-03 08:00:00 0.7 2009-03-03 08:00:00 0.5 2009-03-03 08:00:00要如何在一条查询中实现?谢谢
select t1.s1,t1.s3,t1.s2,
t2.s4,t2.s2,
t3.s5,t3.s2
from
(select t.* from a t where S3 = (select max(s3) from a where S1 = t.S1)) t1,
(select t.* from a t where S4 = (select max(s4) from a where S1 = t.S1)) t2,
(select t.* from a t where S5 = (select max(s5) from a where S1 = t.S1)) t3
where t1.s1 = t2.s1 and t1.s1 = t3.s1
order by t1.s1--2
select t1.s1,t1.s3,t1.s2,
t2.s4,t2.s2,
t3.s5,t3.s2
from
(select t.* from a t where not exists (select max(s3) from a where S1 = t.S1 and s3 > t.s3)) t1,
(select t.* from a t where not exists (select max(s4) from a where S1 = t.S1 and s4 > t.s4)) t2,
(select t.* from a t where not exists (select max(s5) from a where S1 = t.S1 and s5 > t.s5)) t3
where t1.s1 = t2.s1 and t1.s1 = t3.s1
order by t1.s1
from (select t.*
from a t
where S3 = (select max(s3) from a where S1 = t.S1)) t1,
(select t.*
from a t
where S4 = (select max(s4) from a where S1 = t.S1)) t2,
(select t.*
from a t
where S5 = (select max(s5) from a where S1 = t.S1)) t3
where t1.s1 = t2.s1
and t1.s1 = t3.s1
order by t1.s1
select 'a点' s1,to_date('2009-01-01 11:00:00','yyyy-mm-dd hh24:mi:ss') s2,0.1 s3,0.5 s4,0 s5 from dual
union all
select 'b点' s1,to_date('2009-02-02 09:00:00','yyyy-mm-dd hh24:mi:ss') s2,0.2 s3,0.2 s4,1 s5 from dual
union all
select 'c点' s1,to_date('2009-03-03 08:00:00','yyyy-mm-dd hh24:mi:ss') s2,0.2 s3,0.7 s4,0.5 s5 from dual
union all
select 'a点' s1,to_date('2009-04-04 13:00:00','yyyy-mm-dd hh24:mi:ss') s2,0.8 s3,0.4 s4,0.9 s5 from dual
union all
select 'b点' s1,to_date('2009-05-05 09:00:00','yyyy-mm-dd hh24:mi:ss') s2,0.3 s3,0.6 s4,0.7 s5 from dual
)
select t1.s1,t1.s3,t1.s2,
t2.s4,t2.s2,
t3.s5,t3.s2
from
(select t.* from temp t where S3 = (select max(s3) from temp where S1 = t.S1)) t1,
(select t.* from temp t where S4 = (select max(s4) from temp where S1 = t.S1)) t2,
(select t.* from temp t where S5 = (select max(s5) from temp where S1 = t.S1)) t3
where t1.s1 = t2.s1 and t1.s1 = t3.s1
order by t1.s1
FROM (SELECT s1, s2, s3 FROM a WHERE (s1, s3) IN (SELECT s1, MAX(s3) FROM a GROUP BY a.s1)) m3,
(SELECT s1, s2, s4 FROM a WHERE (s1, s4) IN (SELECT s1, MAX(s4) FROM a GROUP BY a.s1)) m4,
(SELECT s1, s2, s5 FROM a WHERE (s1, s5) IN (SELECT s1, MAX(s5) FROM a GROUP BY a.s1)) m5
WHERE m3.s1 = m4.s1 AND
m4.s1 = m5.s1
max(s3)s3,
max(s2)keep(dense_rank last order by s3)s2_1,
max(s4)s3,
max(s2)keep(dense_rank last order by s4)s2_2,
max(s5)s3,
max(s2)keep(dense_rank last order by s5)s2_3
from temp
group by s1