wm_concat()内排序问题

Question

期望： wm_concat()内的按col6排序

 with t as (
select 'A' as col1,'李四' as col2，'1' as col3,'2015-04-15' as col5,'2015-04-15 16:30:30' as col6 from dual
union
select 'B' as col1,'李四' as col2，'2' as col3,'2015-04-15' as col5,'2015-04-15 16:31:30' as col6 from dual
union
select 'C' as col1,'李四' as col2，'3' as col3,'2015-04-15' as col5,'2015-04-15 16:31:50' as col6 from dual
union
select 'AA' as col1,'李四' as col2，'2' as col3,'2015-04-14' as col5,'2015-04-15 16:30:10' as col6 from dual
union
select 'BB' as col1,'李四' as col2，'3' as col3,'2015-04-14' as col5,'2015-04-15 16:31:20' as col6 from dual
union
select 'CC' as col1,'李四' as col2，'2' as col3,'2015-04-14' as col5,'2015-04-15 16:31:30' as col6 from dual
union
select 'X' as col1,'张三' as col2，'1' as col3,'2015-04-15' as col5,'2015-04-15 16:31:50' as col6 from dual
union
select 'Y' as col1,'张三' as col2，'1' as col3,'2015-04-15' as col5,'2015-04-15 16:50:50' as col6 from dual
union
select 'Z' as col1,'张三' as col2，'3' as col3,'2015-04-15' as col5,'2015-04-15 16:51:50' as col6 from dual
union
select 'M' as col1,'王五' as col2，'3' as col3,'2015-04-15' as col5,'2015-04-15 16:31:50' as col6 from dual
)
select
 wm_concat(col1),col2,col5,sum(to_number(col3)) 
from (select * from t order by col6 asc) group by col5,col2 order by col5 desc

Answer 1

with t as (
select 'A' as col1,'李四' as col2，'1' as col3,'2015-04-15' as col5,'2015-04-15 16:30:30' as col6 from dual
union
select 'B' as col1,'李四' as col2，'2' as col3,'2015-04-15' as col5,'2015-04-15 16:31:30' as col6 from dual
union
select 'C' as col1,'李四' as col2，'3' as col3,'2015-04-15' as col5,'2015-04-15 16:31:50' as col6 from dual
union
select 'AA' as col1,'李四' as col2，'2' as col3,'2015-04-14' as col5,'2015-04-15 16:30:10' as col6 from dual
union
select 'BB' as col1,'李四' as col2，'3' as col3,'2015-04-14' as col5,'2015-04-15 16:31:20' as col6 from dual
union
select 'CC' as col1,'李四' as col2，'2' as col3,'2015-04-14' as col5,'2015-04-15 16:31:30' as col6 from dual
union
select 'X' as col1,'张三' as col2，'1' as col3,'2015-04-15' as col5,'2015-04-15 16:31:50' as col6 from dual
union
select 'Y' as col1,'张三' as col2，'1' as col3,'2015-04-15' as col5,'2015-04-15 16:50:50' as col6 from dual
union
select 'Z' as col1,'张三' as col2，'3' as col3,'2015-04-15' as col5,'2015-04-15 16:51:50' as col6 from dual
union
select 'M' as col1,'王五' as col2，'3' as col3,'2015-04-15' as col5,'2015-04-15 16:31:50' as col6 from dual
)
select max(result1) ,col2,col5,max(result2)from (
select wm_concat(col1) over (partition by col5,col2 order by col6) result1,col2,col5,sum(to_number(col3)) over (partition by col5,col2 order by 1 )result2
from t)group by col5,col2 order by col5 desc