整理Oracle分拆合并表

最新推荐文章于 2026-07-28 23:42:06 发布

原创最新推荐文章于 2026-07-28 23:42:06 发布 · 4.2k 阅读

2 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

Oracle

本文详细介绍了如何在Oracle 10g环境中进行表的分拆与合并操作，涵盖了相关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 拆分合并表方法

点击打开链接

标签

#oracle #oracle10g #c #path