SELECT WM_CONCAT(CITY) 城市
FROM (select CITY,type,soft
from t
order 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) citys
from(
select city,soft,type,row_number()over(partition by type order by soft)rn
from t)
start with rn=1
connect by rn=prior rn+1
  and type=prior type
group 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