一个sql，WMSYS.WM_CONCAT函数会改变列转行的顺序

Question

       select a.biz_no,  WMSYS.WM_CONCAT(A.MONITOR_RULE_DETAIL)
                                             
                                 from (select aa.biz_no,
                                              aa.monitor_big_rule,
                                              ROW_NUMBER() OVER(PARTITION BY aa.biz_no ORDER BY aa.biz_no, aa.monitor_big_rule, aa.MONITOR_RULE_DETAIL DESC) || '.' || aa.MONITOR_RULE_DETAIL AS MONITOR_RULE_DETAIL
                                         from CRMS_TEMP_CODE_NAME aa) a
                                group by a.biz_no我原来是 a表里面顺序是 1 2  3  4 5  
使用WMSYS.WM_CONCAT函数转出来 ，顺序就不一样了。
跪求指点

Answer 1

是a表中的 这个列 MONITOR_RULE_DETAIL
还没转行的时候是这样。1.*不良客户办理信贷业务报警，
2.*不良客户办理信贷业务报警21，
3.ccccccccc
4.*典当行业新增贷款报警
5.bbbbb通过上面的函数后是这样
1.*不良客户办理信贷业务报警,3.ccccccccc,4.*典当行业新增贷款报警,5.bbbbb,2.*不良客户办理信贷业务报警

Answer 2

select a.biz_no, WMSYS.WM_CONCAT(A.MONITOR_RULE_DETAIL) 
from (select aa.biz_no, 
             ROW_NUMBER() OVER(PARTITION BY aa.biz_no ORDER BY aa.biz_no
                                                             , aa.monitor_big_rule
                                                             , aa.MONITOR_RULE_DETAIL DESC) 
             || '.' || aa.MONITOR_RULE_DETAIL AS MONITOR_RULE_DETAIL 
      from CRMS_TEMP_CODE_NAME aa) a 
group by a.biz_no;

Answer 3

没用过WMSYS.WM_CONCAT函数
你在子查询里加入一个order by 试试

Answer 4

select a.biz_no,  WMSYS.WM_CONCAT(A.MONITOR_RULE_DETAIL) 
                                            
                                from (select aa.biz_no, 
                                              ROW_NUMBER() OVER(PARTITION BY aa.biz_no ORDER BY aa.biz_no, aa.monitor_big_rule, aa.MONITOR_RULE_DETAIL DESC) || '.' || aa.MONITOR_RULE_DETAIL AS MONITOR_RULE_DETAIL 
                                        from CRMS_TEMP_CODE_NAME aa order by MONITOR_RULE_DETAIL) a 
                                group by a.biz_no 
                                
这样不行？
那你用sys_connect_by_path试试
select a.biz_no, 
 substr(max(sys_connect_by_path(A.MONITOR_RULE_DETAIL,',')),2) 
                                            
                                from (select aa.biz_no, 
                                              ROW_NUMBER() OVER(PARTITION BY aa.biz_no ORDER BY aa.biz_no, aa.monitor_big_rule, aa.MONITOR_RULE_DETAIL DESC) || '.' || aa.MONITOR_RULE_DETAIL AS MONITOR_RULE_DETAIL 
                                        from CRMS_TEMP_CODE_NAME aa order by MONITOR_RULE_DETAIL) a 
start with substr(a.MONITOR_RULE_DETAIL,1,1)='1'
connect by substr(a.MONITOR_RULE_DETAIL,1,1)=prior substr(a.MONITOR_RULE_DETAIL,1,1)-1 and a.biz_no=prior a.biz_no
                                group by a.biz_no 

Answer 5

好像只能用sys_connect_by_path来保证顺序你看看select news_id, wm_concat(stock_code), wm_concat(stock_name)
  from (select * from (select trunc((rownum-1)/4)+1 news_id,
                '0000' || rownum stock_code,
                '名称' || chr(64 + rownum) stock_name 
           from dual
         connect by rownum < 21) news_stk order by news_id, stock_code) group by news_id;

Answer 6

WM_CONCAT函数本来就是不排序的