【2017年整理】Oracle多行记录合并连接聚合字符串的几种方法.docVIP

Oracle多行记录合并/连接/聚合字符串的几种方法 [转]2008-09-13 11:32 怎么合并多行记录的字符串，一直是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 -------------------- -------------------- 中国 台北 中国 香港 中国 上海 日本 东京 日本 大阪 要求得到如下结果集： ------- -------------------- 中国 台北,香港,上海 日本 东京，大阪 实际就是对字符实现一个聚合功能，我很奇怪为什么Oracle没有提供官方的聚合函数来实现它呢：） 下面就对几种经常提及的解决方案进行分析（有一个评测标准最高★★★★★）： 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) -------------------- ---------------- 中国 台北, 香港, 上海 日本