分享
 
 
 

Oracle多行记录字符串综合操作几种方法

王朝oracle·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

怎么合并多行记录的字符串,一直是Oracle新手喜欢问的SQL问题之一,关于这个问题的帖子我看过不下30个了,现在就对这个问题,进行一个总结。

合并

什么是合并多行字符串(连接字符串)呢,例如:

SQL> desc test;

Name Type Nullable Default Comments

------- ------------ -------- ------- --------

COUNTRY VARCHAR2(20) Y

CITY VARCHAR2(20) Y

SQL> select * from test;

COUNTRY CITY

-------------------- --------------------

中国 台北

中国 香港

中国 上海

日本 东京

日本 大阪

要求得到如下结果集:

------- --------------------

中国 台北,香港,上海

日本 东京,大阪

实际就是对字符实现一个聚合功能。

下面就对几种经常提及的解决方案进行分析(有一个评测标准最高★★★★★):

1.被集合字段范围小且固定型:

灵活性★ 性能★★★★ 难度 ★

这种方法的原理在于你已经知道CITY字段的值有几种,且还不算太多,假如太多这个SQL就会相当的长。看例子:

SQL> select t.country,

2 MAX(decode(t.city,'台北',t.city',',NULL))

3 MAX(decode(t.city,'香港',t.city',',NULL))

4 MAX(decode(t.city,'上海',t.city',',NULL))

5 MAX(decode(t.city,'东京',t.city',',NULL))

6 MAX(decode(t.city,'大阪',t.city',',NULL))

7 from test t GROUP BY t.country

8 /

COUNTRY MAX(DECODE(T.CITY,'台北',T.CIT

-------------------- ------------------------------

中国 台北,香港,上海,

日本 东京,大阪,

大家一看,估计就明白了(假如不明白,好好补习MAX DECODE和分组)。这种方法无愧为最笨的方法,但是对某些应用来说,最有效的方法也许就是它。

2.固定表固定字段函数法:

灵活性★★ 性能★★★★ 难度 ★★

此法必须预先知道是哪个表,也就是说一个表就得写一个函数,不过方法1的一个取值就要便捷多了。在大多数应用中,也不会存在大量这种合并字符串的需求。废话完毕,看下面:

定义一个函数:

create or replace function str_list( str_in in varchar2 )

--分类字段

return varchar2 is

str_list varchar2(4000) default null;

--连接后字符串

str varchar2(20) default null;

--连接符号

begin

for x in ( select TEST.CITY from TEST

where TEST.COUNTRY = str_in ) loop

str_list := str_list str to_char(x.city);

str := ', ';

end loop;

return str_list;

end;

使用:

SQL> select DISTINCT(T.country),

list_func1(t.country) from test t;

COUNTRY LIST_FUNC1(T.COUNTRY)

-------------------- ----------------

中国 台北, 香港, 上海

日本 东京, 大阪

SQL> select t.country,str_list(t.country)

from test t GROUP BY t.country;

COUNTRY STR_LIST(T.COUNTRY)

-------------------- -----------------------

中国 台北, 香港, 上海

日本 东京, 大阪

这个时候,使用分组和求唯一都可以满足要求。它的原理就是,根据唯一的分组字段country,在函数里面再次查询该字段对应的所有被合并列,使用PL/SQL将其合并输出。

3.灵活表函数法 灵活性★★★ 性能★★★ 难度 ★★★

该方法是在方法2的基础上,使用动态SQL,将表名和字段名称传入,从而达到灵活的目的。

create or replace function str_list2

( key_name in varchar2,

key in varchar2,

coname in varchar2,

tname in varchar2 )

return varchar2

as

type rc is ref cursor;

str varchar2(4000);

sep varchar2(2);

val varchar2(4000);

cur rc;

begin

open cur for 'select 'coname'

from ' tname '

where ' key_name ' = :x '

using key;

loop

fetch cur into val;

exit when cur%notfound;

str := str sep val;

sep := ', ';

end loop;

close cur;

return str;

end;

SQL> select test.country,

2 str_list2('COUNTRY',

test.country, 'CITY', 'TEST') emplist

3 from test

4 group by test.country

5 /

COUNTRY EMPLIST

-------------------- -----------------

中国 台北, 香港, 上海

日本 东京, 大阪

4.一条SQL语法:

灵活性★★★★ 性能★★ 难度 ★★★★

一条SQL的法则是某位大师提出的,大家曾经在某个时期都乐此不彼的寻求各种的问题一条SQL法,但是大师的意思似乎被曲解,很多性能差,可读性差,灵活差的SQL都是这个原则产物,所谓画虎不成反成犬类。不过,解决问题始终是第一原则,这里还是给出一个比较有代表性的一条SQL方法。

SELECT country,max(substr(city,2)) city

FROM

(SELECT country,sys_connect_by_path(city,',') city

FROM

(SELECT country,city,country

rn rchild,country(rn-1) rfather

FROM

(SELECT test.country ,test.city,row_number() over

(PARTITION BY test.country ORDER BY

test.city) rn

FROM test))

CONNECT BY PRIOR rchild=rfather

START WITH rfather LIKE '%0')

GROUP BY country;

下面分步解析,有4个FROM,就有4次结果集的操作。

step 1 给记录加上序号rn

SQL> SELECT test.country ,test.city,row_number() over

(PARTITION BY test.country ORDER

BY test.city) rn

2 FROM test

3 /

COUNTRY CITY RN

-------------------- -------------------- ----------

日本 大阪 1

日本 东京 2

中国 上海 1

中国 台北 2

中国 香港 3

step 2 创造子节点父节点:

SQL> SELECT country,city,country

rn rchild,country(rn-1) rfather

2 FROM

3 (SELECT test.country ,

test.city,row_number() over

(PARTITION BY test.country ORDER

BY test.city) rn

4 FROM test)

5 /

日本 大阪 日本1 日本0

日本 东京 日本2 日本1

中国 上海 中国1 中国0

中国 台北 中国2 中国1

中国 香港 中国3 中国2

step 3 利用sys_connect_by_path生成结果集:

SELECT country,sys_connect_by_path(city,',') city

FROM

(SELECT country,city,country

rn rchild,country(rn-1) rfather

FROM

(SELECT test.country ,test.city,row_number()

over (PARTITION BY test.country ORDER BY

test.city) rn

FROM test))

CONNECT BY PRIOR rchild=rfather START WITH rfather LIKE '%0'

日本 ,大阪

日本 ,大阪,东京

中国 ,上海

中国 ,上海,台北

中国 ,上海,台北,香港

step 4 最终步骤,筛选结果集合:

SQL> SELECT country,max(substr(city,2)) city

2 FROM

3 (SELECT country,sys_connect_by_path(city,',') city

4 FROM

5 (SELECT country,city,countryrn rchild,country

(rn-1) rfather

6 FROM

7 (SELECT test.country ,test.city,row_number()

over (PARTITION BY test.country ORDER

BY test.city) rn

8 FROM test))

9 CONNECT BY PRIOR rchild=rfather START WITH rfather LIKE '%0')

10 GROUP BY country;

COUNTRY CITY

-------------------- -------

中国 上海,台北,香港

日本 大阪,东京

5.自定义聚合函数:

灵活性★★★★★ 性能★★★★★ 难度 ★★★★★

最后一个方法是我认为“王道”的方法,自定义聚合函数。就如何我在本开始说的,为啥oracle没有这种聚合函数呢?我也不知道,但Oracle提供了聚合函数的API可以让我方便的自己定义聚合函数。

下面给出一个简单的例子:

SQL> SELECT t.country,strcat(t.city)

FROM test t GROUP BY t.country;

COUNTRY STRCAT(T.CITY)

-------------------- ------------------

日本 东京,大阪

中国 台北,香港,上海

简单吧,和官方的函数一样的便捷高效。

函数:

CREATE OR REPLACE FUNCTION strcat(input varchar2 )

RETURN varchar2

PARALLEL_ENABLE AGGREGATE USING strcat_type;

TYPE:

create or replace type strcat_type as object (

cat_string varchar2(4000),

static function ODCIAggregateInitialize

(cs_ctx In Out strcat_type) return number,

member function ODCIAggregateIterate

(self In Out strcat_type,value in varchar2) return

number,

member function ODCIAggregateMerge

(self In Out strcat_type,ctx2 In Out strcat_type)

return number,

member function ODCIAggregateTerminate

(self In Out strcat_type,returnValue Out

varchar2,flags in number) return number

)

6.待发掘:

总结,合并字符串还有更多的方法希望大家能发掘,本文的目的主要是抛砖引玉,假如有新的发现我会继续更新方法。需要注重的问题是,本文采用varchar2为例子,所以长度有限制,Oracle的版本对方法的实现也影响。

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有