整理Oracle分拆合并表

最新推荐文章于 2025-10-26 18:16:39 发布

最新推荐文章于 2025-10-26 18:16:39 发布 · 268 阅读

文章标签：

本文介绍了Oracle数据库中实现表的合并与分拆的方法。针对不同版本的Oracle提供了两种合并表的方法，一种利用wmsys.wm_concat函数，另一种使用connect by路径函数。同时，也给出了两种分拆表的方法，并提供了详细的SQL示例。

Oracle分拆合并表
/**************************************************************************************** Oracle分拆合并表整理人：中國風(Roy) 日期:2011.11.02 *****************************************************************************************/ /**--合并 --模拟数据 Col1 Col2 1 a 1 b 1 c 2 d 2 e 3 f **/ /**--生成结果 COL1 COL2 1 a,b,c 2 d,e 3 f **/ /**oracle10g以上版本字符串函数wmsys.wm_concat**/ /**方法1**/ with Tab as ( select 1 as Col1,'a' as Col2 from dual union all select 1,'b' from dual union all select 1,'c' from dual union all select 2,'d' from dual union all select 2,'e' from dual union all select 3,'f' from dual ) select Col1,wmsys.wm_concat(Col2 ) as Col2 from tab group by Col1 /**oracle9i可以用connect by**/ /**方法2**/ with Tab as ( select 1 as Col1,'a' as Col2 from dual union all select 1,'b' from dual union all select 1,'c' from dual union all select 2,'d' from dual union all select 2,'e' from dual union all select 3,'f' from dual ) select Col1,substr(max(sys_connect_by_path(Col2,',')),2) Col2 from (select a.*,row_number()over(partition by Col1 order by Col1) rn from Tab a ) group by Col1 start with rn=1 connect by rn-1=prior rn and Col1=prior Col1 order by Col1; /**--分拆 --模拟数据 Col1 Col2 1 a,b,c 2 d,e 3 f **/ /**--生成结果 COL1 COL2 1 a 1 b 1 c 2 d 2 e 3 f **/ /**方法1**/ with Tab as (select 1 as Col1,N'a,b,c' as Col2 from dual union all select 2,N'd,e' from dual union all select 3,N'f' from dual ) SELECT Col1,substr(Col2,lev,instr(Col2||',',',',lev)-lev) as Col2 from Tab ,(SELECT LEVEL lev FROM DUAL CONNECT BY LEVEL<=100) WHERE substr(','||Col2,lev,1)=',' /** 条件可换为 instr(','||Col2,',',lev)=lev**/ order by Col1 /**方法2 REGEXP_SUBSTR(srcstr, pattern, position, occurrence, modifier) __srcstr ：检索字符串 __pattern ：匹配模式 __position ：搜索srcstr的起始位置（默认为1） __occurrence：搜索第几次出现匹配模式的字符串（默认为1） __modifier ：检索模式（'i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'。） **/ with Tab as (select 1 as Col1,N'a,b,c' as Col2 from dual union all select 2,N'd,e' from dual union all select 3,N'f' from dual ) SELECT Col1,REGEXP_SUBSTR(Col2,'[^,]+',1,lev) FROM Tab, (SELECT LEVEL lev FROM dual CONNECT BY LEVEL <= 100) b WHERE (LENGTH(Col2)-LENGTH(REPLACE(Col2,',','')))+1 >=lev ORDER BY Col1,lev

SQL Server 拆分合并表方法

点击打开链接