Oracle多行转换成字符串方法总结

Oracle多行转换成字符串方法总结:（注意sys_connect_by_path 分隔符不要用“，”号）
　　当我们在做一些比较复杂的数据导出时，可能会经常遇到要将不固定的多行数据组合成一个字符串返回；ISVP中就遇到了类似的情况，要求对于每一个APP，返回他所属的所有类目名称，类目名称之间用,隔开；对于该类型的问题，总结一下大致有如下几种常见方案；在具体陈述实现方案之前，我们先介绍下我们即将操作的表结构： SQL> desc app_category_link;
　　Name Type Nullable Default Comments
　　-------------------- ------------ -------- ------- -------------
　　APP_CATEGORY_LINK_ID VARCHAR2(20) 主关键
　　APP_ID VARCHAR2(20) 应用ID
　　APP_CATEGORY_ID VARCHAR2(20) 应用类别ID
　　
　　其中字段APP_ID和APP_CATEGORY_ID是一对多关系；
　　
　　方案1：sys_connect_by_path + start with ... connect by ... prior + 分析函数
　　
　　从上面的这个公式中我们可以看出，该方案主要是通过分析函数和父子级联查询来完成，一般是一条SQL搞定，比较省事；首先来看几个具体实现SQL；
　　具体实现1：
　　
　　SELECT app_id,
　　 ltrim(max(sys_connect_by_path(app_category_id, ',')), ',') categ_ids
　　 FROM (SELECT app_id,
　　 app_category_id,
　　 app_category_id || '|' || rn rchild,
　　 app_category_id || '|' || (rn - 1) rfather
　　 FROM (SELECT app_id,
　　 app_category_id,
　　 row_number() over(PARTITION BY app_id ORDER BY app_category_id) rn
　　 FROM app_category_link))
　　START WITH rfather LIKE '%|0'
　　CONNECT BY PRIOR rchild = rfather
　　GROUP BY app_id;
　　
　　具体实现2：
　　
　　select app_id,
　　 ltrim(max(sys_connect_by_path(app_category_id, ',')), ',') categ_ids
　　 from (select t.app_id,
　　 t.app_category_id,
　　 min(t.app_category_id) over(partition by app_id) categ_min,
　　 (row_number() over(order by app_id, app_category_id)) +
　　 (dense_rank() over(order by app_id)) numid
　　 from app_category_link t)
　　start with app_category_id = categ_min
　　connect by numid - 1 = prior numid
　　group by app_id;
　　
　　具体实现3：
　　
　　select app_id,
　　 ltrim(max(sys_connect_by_path(app_category_id, ',')), ',') categ_ids
　　 from (select t.app_id,
　　 t.app_category_id,
　　 (row_number()
　　 over(partition by app_id order by app_category_id)) numid
　　 from app_category_link t)
　　start with numid = 1
　　connect by numid - 1 = prior numid
　　 and app_id = prior app_id
　　group by app_id;
　　
　　具体实现4：
　　
　　select app_id,
　　 ltrim(sys_connect_by_path(app_category_id, ','), ',') categ_ids
　　 from (select t.app_id,
　　 t.app_category_id,
　　 (row_number()
　　 over(partition by app_id order by app_category_id)) numid
　　 from app_category_link t)
　　WHERE connect_by_isleaf = 1
　　start with numid = 1
　　connect by numid - 1 = prior numid
　　 and app_id = prior app_id;
　　
　　请注意看4种实现方式的区别，下面分别介绍下这4种实现方式的具体思路；
　　第1种实现采用了1个分析函数、2次子查询、一个like、以及父子级联查询字段值连接；可以猜测下性能肯定不咋的，2次子查询本来已经很耗时了，对查询出来的结果集还要用like匹配，速度就更慢了，此法可以查询到我们需要的具体数据，但是效率很低，不可取；他的实现思路是利用待查询字段值与各APP下面各类目ID的序列值进行组合，并作为父子关系级联的依据；
　　第2种实现采用了3个分析函数、1次全表扫描、以及父子级联字段值连接；和第1种实现比较而言的话效率会高不少；他的实现思路是利用各APP对应的最小类目ID作为父子级联的开始点，而父子级联的依据是row_number()+dense_rank()，这样做主要是为了避免无限循环；
　　第3、4两种实现思路基本上是一样的，都是1个分析函数、1次全表扫描、以及父子级联字段值连接；从代码长度来说，比前2种实现方式简洁了不少，思路也清晰了很多，直接利用各APP对应类目ID的序列值作为父子级联的开始点和连接依据；但仔细看看两者的SQL，会发现第3这种方式用到了group by子句，而第4种实现却没有用到，而是在where子句中添加了connect_by_isleaf = 1 的查询条件；从性能上来看，应该是第4种实现方式更高，但他只能在10g及其以后的版本中才能使用，connect_by_isleaf 字段是10g中新提供的一个伪列，他可以用来判断该条记录是否是树形记录的叶节点，不过还在用9i版本的可能就有些可惜了；
　　综合以上分析，对4种实现方案，个人推荐使用第3、4两种实现方式，具体哪种可以看所用oracle的版本而定，简而言之，这种实现方式优雅、简洁、高效；
　　
　　方案2：自定义Function/SP
　　
　　在Oracle中提供了非常强大的自定义Function/SP功能，对于该需求，如果说大家觉得方案1中的SQL太长太复杂，那完全可以考虑这种方案；但是和java开发一样，相同的功能不同的人员来实现都会有不同的效果，代码的质量也都会不一样；下面我们就从不同方面来介绍几种function的具体实现方式；
　　实现1：最简单明了、但可扩展性极差；对于我们需要的该表app_category_link写一个特定的function，传入app_id，然后在function内部查询出该app_id对应的所有类目ID信息，然后对结果集循环，最后将连接成的字符串返回；若有很多类似的表都有这样的查询，就得写多个对应的function，所以扩展性极差，但却最简单、有效；
　　实现2：实现较复杂、但可扩展性较好；这种实现方式从实现1演变而来，主要是为了解决多表问题；需要将待查询表名、待查询字段key-value、需连接的字段名 作为参数传入function，function内部会组装这些参数并形成select语句，查询并对返回结果集循环连接；
　　实现3：实现简单、可扩展性好；不会吧！鱼和熊掌能够兼得？呵呵，当然可以，实现2中的思路值得借鉴，但是扩展性仍然不够，因为function内部需要组装查询sql，当然最好的方式是分工明确，function中只针对结果集进行循环连接，而不需要关心结果集是如何得来的，这和实际java开发有异曲同工之妙！
　　针对以上3种实现方式，很明显实现3占据几乎所有的优势，推荐采用；以下贴一段参考代码；
　　function实现：
　　
　　create or replace type strings_table is table of varchar2(20);
　　/
　　create or replace function mymerge (pv in strings_table) return varchar2
　　is
　　 ls varchar2(4000);
　　begin
　　 for i in 1..pv.count loop
　　 ls := ls || ',' || pv(i);
　　 end loop;
　　 return ls;
　　end;
　　/
　　
　　具体调用function的SQL实现：
　　
　　select t0.app_id,
　　 mymerge(cast(multiset (select t.app_category_id
　　 from app_category_link t
　　 where t.app_id = t0.app_id) as strings_table)) categ_ids
　　 from (select distinct app_id from app_category_link) t0;
　　
　　方案3：Oracle 10g + wmsys.wm_concat
　　
　　可能我们提到的这个功能太普遍了，oracle在10g及其以上版本提供了一个内置函数，可以搞定类似需求，用了这个function之后代码变得异常简单，只能说太优雅喽；具体参见如下SQL：
　　
　　select app_id, wmsys.wm_concat(app_category_id) categ_ids
　　 from app_category_link t
　　group by t.app_id;
　　
　　注意：该函数好像不支持对待查询字段排序，也就是说无法对app_category_id连接的顺序进行指定；若要完成该功能，只能利用子查询先用order by排序好，然后再调用该函数；
　　另外，由于该函数是加密过的，看不到源代码，所以不知道function内部具体是如何实现的，是不是采用我们上面方案2中所提到的某一种呢？
　　其实在10g里面，还有一个MODEL SQL，也可以实现类似的功能，不过我还没有研究过，暂时略过，有兴趣的同学可以先研究研究；
　　
　　上面提到了很多的针对不固定行转换成字符串的实现方案，从各方案的比较来看，当然推荐用户升级到10g，这样就有很多的高级功能可以使用了；不过在现实需求中，还有一些是固定行转换成字符串，对于这种需求想象下，实现应该会更简单，呵呵，但实际效果却并没有我们想象中那么好，其实这些实现都是人肉级的查询，那都有哪些实现方式呢？
　　其一，MAX + DECODE / CASE：该组合经常被用于固定行转换成固定多列，我们只要稍微变化下就可以满足我们的固定行转换成字符串的需求了，用 || 或者concat函数将各列连接起来即可；
　　其二，ROW_NUMBER + LEAD：这种组合主要是利用分析函数来实现，因为分析函数可以很容易的提取到当前记录之前或者之后N条记录的值，所以当我们明确知道有多少行需要连接组合时，就可以利用LEAD这个分析函数来搞定；
　　
　　总体而言
　　1.自定义函数实现
　　虽然这种方式可以实现需求，但是如果表t的数据量很大，i的值又很多的情况下，因为针对每个i值都要执行一句select，扫描和排序的次数和i的值成正比，性能会非常差。
　　2.使用sys_connect_by_path
　　从执行计划上来看，这种方式只需要扫描两次表，比自定义函数的方法，效率要高很多，尤其是表中数据量较大的时候：
　　3.使用wm_sys.wm_concat
　　这个函数也可以实现类似的行列转换需求，但是似乎没有办法做到直接根据另外一列排序，所以需要先通过子查询或者临时表排好序
　　执行计划上看，只需要做一次表扫描就可以了，但是这个函数是加密过的，执行计划并不能显示函数内部的操作。
　　
　　例子
　　Oracle行转列----04多行转换成字符串
　　 CREATE TABLE t_row_str(
　　 ID INT,
　　 col VARCHAR2(10));
　　 INSERT INTO t_row_str VALUES(1,'a');
　　 INSERT INTO t_row_str VALUES(1,'b');
　　 INSERT INTO t_row_str VALUES(1,'c');
　　 INSERT INTO t_row_str VALUES(2,'a');
　　 INSERT INTO t_row_str VALUES(2,'d');
　　 INSERT INTO t_row_str VALUES(2,'e');
　　 INSERT INTO t_row_str VALUES(3,'c');
　　 COMMIT;
　　 SELECT * FROM t_row_str;
　　 5.1
　　 MAX + DECODE
　　 适用范围：8i,9i,10g及以后版本
　　 SELECT id,
　　 MAX(decode(rn, 1, col, NULL)) ||
　　 MAX(decode(rn, 2, ',' || col, NULL)) ||
　　 MAX(decode(rn, 3, ',' || col, NULL)) str
　　 FROM (SELECT id,
　　 col,
　　 row_number() over(PARTITION BY id ORDER BY col) AS rn
　　 FROM t_row_str) t
　　 GROUP BY id
　　 ORDER BY 1;
　　 5.2
　　 ROW_NUMBER + LEAD
　　 适用范围：8i,9i,10g及以后版本
　　 SELECT id, str
　　 FROM (SELECT id,
　　 row_number() over(PARTITION BY id ORDER BY col) AS rn,
　　 col || lead(',' || col, 1) over(PARTITION BY id ORDER BY col) ||
　　 lead(',' || col, 2) over(PARTITION BY id ORDER BY col) ||
　　 lead(',' || col, 3) over(PARTITION BY id ORDER BY col) AS str
　　 FROM t_row_str)
　　 WHERE rn = 1
　　 ORDER BY 1;
　　 5.3
　　 MODEL
　　 适用范围：10g及以后版本
　　 SELECT id, substr(str, 2) str FROM t_row_str
　　 MODEL
　　 RETURN UPDATED ROWS
　　 PARTITION BY(ID)
　　 DIMENSION BY(row_number() over(PARTITION BY ID ORDER BY col) AS rn)
　　 MEASURES (CAST(col AS VARCHAR2(20)) AS str)
　　 RULES UPSERT
　　 ITERATE(3) UNTIL( presentv(str[iteration_number+2],1,0)=0)
　　 (str[0] = str[0] || ',' || str[iteration_number+1])
　　 ORDER BY 1;
　　 5.4
　　 SYS_CONNECT_BY_PATH
　　 适用范围：8i,9i,10g及以后版本
　　 SELECT t.id id, MAX(substr(sys_connect_by_path(t.col, ','), 2)) str
　　 FROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) rn
　　 FROM t_row_str) t
　　 START WITH rn = 1
　　 CONNECT BY rn = PRIOR rn + 1
　　 AND id = PRIOR id
　　 GROUP BY t.id;
　　 适用范围：10g及以后版本
　　 SELECT t.id id, substr(sys_connect_by_path(t.col, ','), 2) str
　　 FROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) rn
　　 FROM t_row_str) t
　　 WHERE connect_by_isleaf = 1
　　 START WITH rn = 1
　　 CONNECT BY rn = PRIOR rn + 1
　　 AND id = PRIOR id;
　　 5.5
　　 WMSYS.WM_CONCAT
　　 适用范围：10g及以后版本
　　 这个函数预定义按','分隔字符串，若要用其他符号分隔可以用，replace将','替换。
　　 SELECT id, REPLACE(wmsys.wm_concat(col), ',', '/') str
　　 FROM t_row_str

　　 GROUP BY id;