小组网站建设方案书/自己如何制作网站

原贴地址 http://www.itpub.net/477803.html
在put上经常见到有人问"字符串连接"或是"一列多行数据怎么变为一列一行", 在此汇总我所知比较好的几种字符串聚合的方法, 供大家参考.

在这里生成测试数据, 后面详细介绍方法的时候需要用到.

---

  create table tb_agg(
    val   varchar2(4),
    gcol  varchar2(4)
  )
  /
  
  insert into tb_agg 
  select lpad(rownum,2,'0'), ceil(rownum/5) from dual connect by rownum<=20;
  commit;

---

1. 通过 sys_connect_by_path
   需要对数据进行树型构造, 在通过sys_connect_by_path完成字符串的的聚合.
   这种方法便于对聚合元素排序, 但是元素与元素间必须要有分割符(如不需要分割符, 需要最后replace掉), 且聚合后的数据不能超过varchar2最大长度的限制.
示例: (该方法中需要用到分析函数来构造树型结构, 并用connect by进行查询)

---

  SQL> with t as (
    2  select val, gcol,
    3         lag(val) over(partition by gcol order by val) as pval1
    4    from tb_agg
    5  )
    6  select gcol, max(sys_connect_by_path(val, '/')) as aggr
    7    from t
    8   start with pval1 is null
    9   connect by prior val=pval1
   10   group by gcol
   11  /
  
  GCOL     AGGR
  -------- ----------------------------------------
  1        /01/02/03/04/05
  2        /06/07/08/09/10
  3        /11/12/13/14/15
  4        /16/17/18/19/20

---

   
2. 通过自定义聚合函数完成
   需要用到oracle提供的自定义聚合函数的方法, 先定义聚合type, 然后定义函数, 来完成字符串的聚合.
   这种方法可以任意指定分割符或无分割符, 聚合长度可以通过根据type中定义的不同, 返回varchar2或clob, 因此可以不受varchar2上限限制, 但是对聚合元素排序的话需要特殊处理.
   由于自定义聚合函数较长, 请参见 http://www.itpub.net/477803.html

---

3. 通过自定义集合实现
   需要自定义集合类型, 并定义集合聚合函数.
   这种方法的特定和自定义聚合函数基本类似.
   详细参见: 字符串聚合之自定义集合

---

-- 注意声明时的长度, 合适的长度最佳, 不要过长, 也不要过小
create or replace type vars is table of varchar2(1000)
/

-- 返回为varchar2, 有 varchar2 大小上限限制的
create or replace function sumvar (p_in in vars) return varchar2
is
  v_out varchar2(4000);
begin
  for i in 1..p_in.count loop
    v_out := v_out || p_in(i);
  end loop;
  return v_out;
end;
/

-- 返回clob, 无varchar2上限限制
create or replace function sumvarc (p_in in vars) return clob
is
  v_out clob;
begin
  for i in 1..p_in.count loop
    v_out := v_out || p_in(i);
  end loop;
  return v_out;
end;
/

---

函数的使用示例:

---

  SQL> select gcol,
    2         sumvar(cast(multiset(select '/'||val
    3                                from tb_agg
    4                               where a.gcol=gcol
    5                               order by val) as vars
    6                    )
    7               ) as aggr
    8    from tb_agg a
    9   group by gcol
   10  /
  
  GCOL     AGGR
  -------- ----------------------------------------
  1        /01/02/03/04/05
  2        /06/07/08/09/10
  3        /11/12/13/14/15
  4        /16/17/18/19/20