当前位置: 代码迷 >> Oracle技术 >> WM_CONCAT 排序有关问题,困扰了很久,
  详细解决方案

WM_CONCAT 排序有关问题,困扰了很久,

热度:59   发布时间:2016-04-24 08:29:49.0
WM_CONCAT 排序问题,困扰了很久,求助!
WITH T AS  
(
 SELECT 2 AS ID , '沈阳市' AS CITY, '1' TYPE,2 SOFT FROM DUAL 
 UNION ALL  
 SELECT 3 AS ID , '大连市', '1' TYPE, 1 SOFT FROM DUAL 
 UNION ALL  
 SELECT 5 AS ID , '长春市', '2' TYPE , 3 SOFT FROM DUAL 
 UNION ALL  
 SELECT 6 AS ID , '延边市', '2' TYPE , 1 SOFT FROM DUAL  
)

 SELECT WM_CONCAT(CITY) 城市
  FROM T
  GROUP BY TYPE

我想查询完后的结果根据soft字段排序,如下

大连市,沈阳市
延边市,长春市

哪位高手能帮我解答,困扰了我很久!


------解决方案--------------------
那先排序再组合

SQL code
SELECT WM_CONCAT(CITY) 城市FROM (select CITY,type,softfrom torder by soft)GROUP BY TYPE
------解决方案--------------------
什么问题?
------解决方案--------------------
探讨
那先排序再组合


SQL code

SELECT WM_CONCAT(CITY) 城市
FROM (select CITY,type,soft
from t
order by soft)
GROUP BY TYPE

------解决方案--------------------
--wm_concat排序方法
WITH t AS
( SELECT 1 AS sno, 1 AS cno,5 AS grade FROM dual
UNION ALL
SELECT 1, 2, 6 FROM dual
UNION ALL
SELECT 1, 3, 10 FROM dual
UNION ALL
SELECT 2, 3, 10 FROM dual
UNION ALL
SELECT 2, 2, 10 FROM dual
UNION ALL
SELECT 3, 1, 9 FROM dual
UNION ALL
SELECT 3, 2, 9 FROM dual
)
SELECT sno,
MAX(aa)
FROM
( SELECT sno,wm_concat(cno)over(partition BY sno order by cno) AS aa FROM t
) a
GROUP BY sno;

SNO MAX(AA)
---------------------- -------
1 1,2,3
2 2,3
3 1,2
------解决方案--------------------
这种方式比较危险,记得有过先排序后,wm_concat不认账的案例。是否排序和执行计划有关,wm_concat并不保证排序

如果数据量不大的话,可以用树形查询来做
SQL code
select type,substr(max(sys_connect_by_path(city,',')),2) citysfrom(select city,soft,type,row_number()over(partition by type order by soft)rnfrom t)start with rn=1connect by rn=prior rn+1  and type=prior typegroup by type;
------解决方案--------------------
用5楼的窗口就完事了,只不过max用法是错的,字符串max是找ascii码最大的,不是找最长的
SQL code
select type,cities from (  select type ,wm_concat(city) over(partition by type order by soft rows between unbounded preceding and unbounded following) cities from t) group by type,cities