示例:
Co1 Co2 Co3
A time1(时间数值) value1
A time2(时间数值) value2
A time3(时间数值) value3
B time4(时间数值) value4
B time5(时间数值) value5
B time6(时间数值) value6 表数据如上 要求按照Co1分组,求出Co1每个分组的总数并求出分组中Co2时间值最大对应的(Co3)value(如果时间有一样,那么任意取一个value值
结果(假设time1,time2,time3中time3最大, time4,time5,time6中time5值最大)
A 3 time3 value3
B 3 time5 value5 SQL语句如何写呢
本来这样处理的,
select co1 ,count(*) ,max(co2desc),max(co3desc) from (select a,co2 co2desc,
first_value(Co3) partion by A order by Co2 desc co3desc from 上表)
) group by co1可能写的有语法错误。。
觉得上面写的不好,望各位大侠指教!
Co1 Co2 Co3
A time1(时间数值) value1
A time2(时间数值) value2
A time3(时间数值) value3
B time4(时间数值) value4
B time5(时间数值) value5
B time6(时间数值) value6 表数据如上 要求按照Co1分组,求出Co1每个分组的总数并求出分组中Co2时间值最大对应的(Co3)value(如果时间有一样,那么任意取一个value值
结果(假设time1,time2,time3中time3最大, time4,time5,time6中time5值最大)
A 3 time3 value3
B 3 time5 value5 SQL语句如何写呢
本来这样处理的,
select co1 ,count(*) ,max(co2desc),max(co3desc) from (select a,co2 co2desc,
first_value(Co3) partion by A order by Co2 desc co3desc from 上表)
) group by co1可能写的有语法错误。。
觉得上面写的不好,望各位大侠指教!
SQL> WITH ab AS
2 (SELECT 1 a ,20090101 b ,3 c FROM dual
3 UNION
4 SELECT 1,20090102,4 FROM dual
5 UNION
6 SELECT 1,20090103,5 FROM dual
7 UNION
8 SELECT 2,20090101,3 FROM dual
9 UNION
10 SELECT 2,20090102,4 FROM dual
11 UNION
12 SELECT 2,20090103,5 FROM dual)
13 select a,count(1),max(b),
14 max(c)keep(dense_rank last order by b)
15 from ab
16 group by a
17 ;
A COUNT(1) MAX(B) MAX(C)KEEP(DENSE_RANKLASTORDER
---------- ---------- ---------- ------------------------------
1 3 20090103 5
2 3 20090103 5
SQL>
WITH ab AS
(SELECT 1 a ,20090101 b ,3 c FROM dual
UNION
SELECT 1,20090102,4 FROM dual
UNION
SELECT 1,20080103,5 FROM dual
UNION
SELECT 2,20090101,3 FROM dual
UNION
SELECT 2,20090102,4 FROM dual
UNION
SELECT 2,20090103,5 FROM dual)
SELECT DISTINCT a,MAX(b)over(PARTITION BY a order BY b DESC ) b,
MAX(c)over(PARTITION BY a order BY c DESC ) c FROM ab