这个SELECT 怎么实现

jiaping0424

如何让这个语句不实现排序：
with temp as(  
  SELECT 'Guangzhou 1' city from dual union all  
  SELECT 'Shanghai 2' city from dual union all  
  SELECT 'Beijing 3' city from dual union all  
  SELECT 'New York 4' city from dual union all  
  SELECT 'Bostom 5' city from dual union all  
  SELECT 'Tokyo 6' city from dual   
)  
SELECT listagg(city,' ')   within GROUP (ORDER BY city)  
from temp ;


我想通过一个SELECT实现把多行变成一行，根据行从上到下拼接成一行（不使用ROWNUM），不排序，如何实现，求高手指点？

隐剑埋名

with temp as(  
  SELECT 'Guangzhou 1' city from dual union all  
  SELECT 'Shanghai 2' city from dual union all  
  SELECT 'Beijing 3' city from dual union all  
  SELECT 'New York 4' city from dual union all  
  SELECT 'Bostom 5' city from dual union all  
  SELECT 'Tokyo 6' city from dual   
)  
SELECT to_char(wm_concat(city))
from temp;

jiaping0424

隐剑埋名 发表于 2015-12-24 17:57
with temp as(  
  SELECT 'Guangzhou 1' city from dual union all  
  SELECT 'Shanghai 2' city from  ...

wm_concat 此函数在12C中没有

〇〇

order by null

abstractcyj

with temp as(  
  SELECT 'Guangzhou 1' city from dual union all  
  SELECT 'Shanghai 2' city from dual union all  
  SELECT 'Beijing 3' city from dual union all  
  SELECT 'New York 4' city from dual union all  
  SELECT 'Bostom 5' city from dual union all  
  SELECT 'Tokyo 6' city from dual   
)  
SELECT listagg(city,' ')   within GROUP (ORDER BY regexp_substr(city, '[0-9]+',1, 1))  
from temp ;

〇〇

abstractcyj 发表于 2015-12-24 19:56
with temp as(  
  SELECT 'Guangzhou 1' city from dual union all  
  SELECT 'Shanghai 2' city from  ...

还要to_number

〇〇

测试了，order by null是不行的，参考5+6楼

solomon_007

with temp as(  
  SELECT 'Guangzhou 1' city from dual union all  
  SELECT 'Shanghai 2' city from dual union all  
  SELECT 'Beijing 3' city from dual union all  
  SELECT 'New York 4' city from dual union all  
  SELECT 'Bostom 5' city from dual union all  
  SELECT 'Tokyo 6' city from dual   
)  
SELECT listagg(city,' ')   within GROUP (ORDER BY substr(city,-1,1))  
from temp ;

〇〇

solomon_007 发表于 2015-12-24 20:13
with temp as(  
  SELECT 'Guangzhou 1' city from dual union all  
  SELECT 'Shanghai 2' city from  ...

也不能处理〉10

solomon_007

   那不用rownum,允许用 row_number也行啊,把temp加一层就是啦