WM_CONCAT 排序问题，困扰了很久，求助！

[tuotuoomega]

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字段排序，如下

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

哪位高手能帮我解答，困扰了我很久！

[大明湖畔_帅锅]

那先排序再组合

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

[tuotuoomega]

那先排序再组合


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

这种方式 在我的数据里有问题！！！！！！！！！！！！！！

[大明湖畔_帅锅]

什么问题？

[ssqtjffcu]

[Quote=引用 1 楼 的回复:]
那先排序再组合


SQL code

SELECT WM_CONCAT(CITY) 城市
FROM (select CITY,type,soft
from t
order by soft)
GROUP BY TYPE
[/Quote]
很好，正确！！！

[hupeng213]

--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

[小灰狼W]

这种方式比较危险，记得有过先排序后，wm_concat不认账的案例。是否排序和执行计划有关，wm_concat并不保证排序

如果数据量不大的话，可以用树形查询来做

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;

[forgetsam]

用5楼的窗口就完事了，只不过max用法是错的，字符串max是找ascii码最大的，不是找最长的

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

[若行若冲]

最终解决方案： 最终解决思路： SQL> select m, max(r) 2 from (select m, wm_concat(n) over (partition by m order by n) r from t) 3 group by m ; M MAX(R) ———- ——————————————————————————– 1 0,1,10,11 2 2,7,12,17 3 3,5,13,15 5 6,16 利用 wm_concat 生成索引引用的字段 with sql_tmp as (select INDEX_NAME, wm_concat(COLUMN_POSITION) over(partition by INDEX_NAME order by COLUMN_POSITION) COLUMN_POSITION, wm_concat(COLUMN_NAME) over(partition by INDEX_NAME order by COLUMN_NAME) COLUMN_NAME from user_ind_columns) select INDEX_NAME, max(COLUMN_POSITION), max(COLUMN_NAME) from sql_tmp group by INDEX_NAME ；

[wangdaoer2]

我可是被这个wm_concat整的不行了，以上两种方式都不能解决我的问题。。。。还看到过一种，复杂了点没懂。。

[idea1861]

SELECT FM_UNI_CODE,MAX(FM_NAME),MAX(PAR_NAME_ZH) FROM( SELECT FM_UNI_CODE,CAST(ZH_CONCAT(FM_NAME) OVER(PARTITION BY FM_UNI_CODE ORDER BY FM_NAME DESC) AS VARCHAR2(3000)) FM_NAME , CAST(ZH_CONCAT(PAR_NAME_ZH) OVER(PARTITION BY FM_UNI_CODE ORDER BY FM_NAME DESC) AS VARCHAR2(3000)) AS PAR_NAME_ZH FROM TI_FM_BANK_ASSET_INFO LEFT JOIN (SELECT PAR_CODE,PAR_SYS_CODE,PAR_NAME_ZH FROM TC_PUB_PAR WHERE PAR_SYS_CODE=10025 ORDER BY PAR_CODE DESC) TC_PUB_PAR ON TI_FM_BANK_ASSET_INFO.FM_NAME=TC_PUB_PAR.PAR_CODE WHERE TC_PUB_PAR.PAR_SYS_CODE=10025 AND FM_UNI_CODE=107011027 GROUP BY FM_UNI_CODE ,FM_NAME,PAR_NAME_ZH ) GROUP BY FM_UNI_CODE

[idea1861]

SELECT FM_UNI_CODE,MAX(FM_NAME),MAX(PAR_NAME_ZH) FROM( SELECT FM_UNI_CODE,CAST(wm_concat(FM_NAME) OVER(PARTITION BY FM_UNI_CODE ORDER BY FM_NAME DESC) AS VARCHAR2(3000)) FM_NAME , CAST(wm_concat(PAR_NAME_ZH) OVER(PARTITION BY FM_UNI_CODE ORDER BY FM_NAME DESC) AS VARCHAR2(3000)) AS PAR_NAME_ZH FROM TI_FM_BANK_ASSET_INFO LEFT JOIN (SELECT PAR_CODE,PAR_SYS_CODE,PAR_NAME_ZH FROM TC_PUB_PAR WHERE PAR_SYS_CODE=10025 ORDER BY PAR_CODE DESC) TC_PUB_PAR ON TI_FM_BANK_ASSET_INFO.FM_NAME=TC_PUB_PAR.PAR_CODE WHERE TC_PUB_PAR.PAR_SYS_CODE=10025 AND FM_UNI_CODE=107011027 GROUP BY FM_UNI_CODE ,FM_NAME,PAR_NAME_ZH ) GROUP BY FM_UNI_CODE

[zhangkaihua2010]

select A.TYPE,max(KEY)
from
(
        select TYPE,
               WMSYS.WM_CONCAT(CITY) OVER(PARTITION BY TYPE ORDER BY SOFT,ID) KEY,
               row_number() over(PARTITION BY TYPE ORDER BY SOFT) rs  
        from
        (
                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
                order by SOFT
        )
)A
group by A.TYPE

执行效果如下：

[zhangkaihua2010]