现有如下的表
| 名字 | 省份 |
|---|---|
| 宝姐 | 黑龙江 |
| 佛爷 | 吉林 |
| 道爷 | 吉林 |
| 翁同学 | 黑龙江 |
要求将省份相邻的记录放在一起,不能以任何形式使用ORDER BY
SQL> with dao_t as
2 (select'宝姐' name1, '黑龙江' sheng from dual
3 union all
4 select'佛爷', '吉林' from dual
5 union all
6 select'道爷', '吉林' from dual
7 union all
8 select'翁同学', '黑龙江' from dual )
9 select sheng daoheng ,substr(name_str,instr(name_str,',',1,x)+1,instr(name_str,',',1,x+1)-instr(name_str,',',1,x)-1) shangqian
10 from (select sheng,','||listagg(name1,',') within group ( order by name1 ) ||',' name_str
11 from dao_t
12 group by sheng ) ,
13 (select rownum n from dual connect by level <=10),
14 (select rownum x from dual connect by level <=10)
15 where n= length(name_str)-length(replace(name_str,',',null))-1
16 and x<=n ;
DAOHENG SHANGQIAN
--------- --------------------------------------------------------------------------------
吉林 道爷
吉林 佛爷
黑龙江 翁同学
黑龙江 宝姐