那先排序再组合
SELECT WM_CONCAT(CITY) 城市
FROM (select CITY,type,soft
from t
order by soft)
GROUP BY TYPE
SELECT WM_CONCAT(CITY) 城市
FROM (select CITY,type,soft
from t
order by soft)
GROUP BY TYPE
那先排序再组合
SELECT WM_CONCAT(CITY) 城市
FROM (select CITY,type,soft
from t
order by soft)
GROUP BY TYPE这种方式 在我的数据里有问题!!!!!!!!!!!!!!
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
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;
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
;
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
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
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执行效果如下: