--10g select dc,replace(wm_concat(mc),',','') mc from table_name group by dc;
这个简单啊。。 select DM ,replace(WM_CONCATE(mc),',','') MC from tb;10G及以上版本使用 10G以下版本自定函数写个
select dm,replace(wm_concat(mc),',','') from tb
select dm,replace(wm_concat(mc),',','') from tb group by dm
--上面我没考虑去重复数据,修改如下: WITH tab AS( SELECT '01' dc, '12' mc FROM dual UNION ALL SELECT '01', '13' FROM dual ) SELECT dc,replace(wm_concat(mc),',','') mc FROM( SELECT DISTINCT dc,SubStr(mc,LEVEL,1) mc FROM tab CONNECT BY LEVEL<=Length(mc) ) GROUP BY dc;DC MC --------- 01 123
我用的数据库是oracle92版本的是否不支持wm_concat??
那在oracle92下除了自己写函数还,就没其他办法?
9I确实没有这样的系统函数,你只有写个和wm_concat一样的函数。
--现在9i可以了吧 ,用sys_connect_by_path: WITH tab AS( SELECT '01' dc, '12' mc FROM dual UNION ALL SELECT '01', '13' FROM dual ) SELECT dc,Max(REPLACE(sys_connect_by_path(mc,','),',','')) mc FROM( SELECT dc,mc,Row_Number()over(PARTITION BY dc ORDER BY mc) rn FROM( SELECT DISTINCT dc,SubStr(mc,LEVEL,1) mc FROM tab CONNECT BY LEVEL<=Length(mc) ) ) START WITH rn=1 CONNECT BY PRIOR rn = rn-1 AND PRIOR dc=dc GROUP BY dc;DC MC --------- 01 123
--10g
select dc,replace(wm_concat(mc),',','') mc from table_name group by dc;
select DM ,replace(WM_CONCATE(mc),',','') MC from tb;10G及以上版本使用 10G以下版本自定函数写个
--上面我没考虑去重复数据,修改如下:
WITH tab AS(
SELECT '01' dc, '12' mc FROM dual UNION ALL
SELECT '01', '13' FROM dual
)
SELECT dc,replace(wm_concat(mc),',','') mc FROM(
SELECT DISTINCT dc,SubStr(mc,LEVEL,1) mc FROM tab CONNECT BY LEVEL<=Length(mc)
) GROUP BY dc;DC MC
---------
01 123
WITH tab AS(
SELECT '01' dc, '12' mc FROM dual UNION ALL
SELECT '01', '13' FROM dual
)
SELECT dc,Max(REPLACE(sys_connect_by_path(mc,','),',','')) mc FROM(
SELECT dc,mc,Row_Number()over(PARTITION BY dc ORDER BY mc) rn FROM(
SELECT DISTINCT dc,SubStr(mc,LEVEL,1) mc FROM tab CONNECT BY LEVEL<=Length(mc)
)
)
START WITH rn=1
CONNECT BY PRIOR rn = rn-1 AND PRIOR dc=dc
GROUP BY dc;DC MC
---------
01 123