Oracle LISTAGG()函数

使用 LISTAGG() WITHIN GROUP () 将多行数据合并成一行。

With t1 as (
        select 1 id,'AA' name_ , to_date('2019-08-17 22:14:20','yyyy-MM-dd HH24:mi:ss') createDate from dual
 UNION  select 2 id,'BB' name_ , to_date('2019-08-16 22:14:20','yyyy-MM-dd HH24:mi:ss') createDate from dual
 UNION  select 3 id,'CC' name_ , to_date('2019-08-10 22:14:20','yyyy-MM-dd HH24:mi:ss') createDate from dual
 UNION  select 4 id,'DD' name_ , to_date('2019-08-10 22:14:20','yyyy-MM-dd HH24:mi:ss') createDate from dual
 UNION  select 5 id,'AA' name_ , to_date('2019-08-08 22:14:20','yyyy-MM-dd HH24:mi:ss') createDate from dual
 UNION  select 6 id,'BB' name_ , to_date('2019-08-15 22:14:20','yyyy-MM-dd HH24:mi:ss') createDate from dual
 UNION  select 7 id,'CC' name_ , to_date('2019-08-16 22:14:20','yyyy-MM-dd HH24:mi:ss') createDate from dual
 UNION  select 8 id,'DD' name_ , to_date('2019-08-08 22:14:20','yyyy-MM-dd HH24:mi:ss') createDate from dual
)
select listagg(id,',')within group(order by createDate desc ) ids_ from t1

根据某字段分组，查询多行到数据，并合成一行数据

select name_,listagg(id,',')within group(order by createDate desc ) ids_ from t1 group by name_