1 连接符号“||”的使用，能将多个表中不同的字段进行拼接。

1）单表的两个字段拼接

select  bxze||'#'||jkdh from bx_bxdj

结果：55471#BX1200025

2）多表联合查询，表间字段的拼接，bmmc为gg_bmxx中的字段，jkdh为bx_bxdj表的字段

select  bmmc||'#'||jkdh from bx_bxdj,gg_bmxx where bx_bxdj.bmxh=gg_bmxx.id
结果：工程造价中心#BX1200274

2 树形结构结果查询方法

 参考原文 http://blog.csdn.net/precipitant/article/details/1427566

 SQL>   select   no,q   from   test  
      2     /  
   
  NO                   Q  
  ----------   ------------------------------  
  001                 n1  
  001                 n2  
  001                 n3  
  001                 n4  
  001                 n5  
  002                 m1  
  003                 t1  
  003                 t2  
  003                 t3  
  003                 t4  
  003                 t5  
  003                 t6   
  12   rows   selected  

  求得如下的结果：  
  001                 n1;n2;n3;n4;n5  
  002                 m1  
  003                 t1;t2;t3;t4;t5;t6  

解题：对表数据进行分析，进行编号和分组编号，如下图中的rn，rn1

http://s15/mw690/99201d89td4a65edb808e&690
 将上述的sql作为子查询，对a=001的数据进行拼接
http://s3/mw690/99201d89td4a65f5e7782&690

进一步帅选

select t.*,
       (select max(sys_connect_by_path(b, ';')) result
          from (select a,
                       b,
                       rn,
                       lead(rn) over(partition by a order by rn) rn1
                  from (select a,
                               b,
                               row_number() over(order by a, b desc) rn
                          from lucheng))
         start with a = t.a
                and rn1 is null
        connect by rn1 = prior rn) value
  from (select distinct a from lucheng) t

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

A=select a,b,row_number() over(order by a, b desc) rn from lucheng 最初的排序rn=1-12

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

select a,
                       b,
                       rn,
                       lead(rn) over(partition by a order by rn) rn1
                  from (A) --新增一列r1

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

结果

a        value

002      ;m1

001      ;n1;n2;n3;n4;n5

003      ;t1;t2;t3;t4;t5;t6

3 航程问题

a    b     c    d 

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

999  x     y    1

999  y     z    2

999  z     m    3

999  m     l    4
求得999的航线是x-y-z-m-l.

select t1.b || '-' || t2.b

from (select a, b from A where d = 1) t1,

    (select a, replace(wm_concat(c), ',', '-') b from A start with d = 1 connect by b = prior c group by a) t2
where t1.a = t2.a
简化格式：select x from (sql1)t1,(sql2)t2 where t1.a=t2.a

replace('x','y','z')函数表示将x字符串中的y符号同z符号代替

如若不使用替换函数

select a, wm_concat(c) b
          from lucheng --where d<5
         start with d = 1
        connect by b = prior c
         group by a

结果  a  b ---> 999  y,z,m,l                 wm_concat函数默认用，号分隔

sql1的值为

a   b

999  x

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

sql2的值为

a    b

999 y-z-m-l

4 WMSYS.WM_CONCAT 函数的用法

参考原文 http://blog.sina.com.cn/s/blog_5c4736800100l2qw.html

SQL> select * from idtable;--所有数据

        ID NAME

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

        10 ab

        10 bc

        10 cd

        20 hi

        20 ij

        20 mn

6 rows selected

SQL> select id,wmsys.wm_concat(name) name from idtable group by id;

        ID NAME

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

        10 ab,bc,cd

        20 hi,ij,mn

SQL> select id,wmsys.wm_concat(name) over (order by id) name from idtable;

        ID NAME

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

        10 ab,bc,cd

        10 ab,bc,cd

        10 ab,bc,cd

        20 ab,bc,cd,hi,ij,mn

        20 ab,bc,cd,hi,ij,mn

        20 ab,bc,cd,hi,ij,mn

6 rows selected

SQL> select id,wmsys.wm_concat(name) over (order by id,name) name from idtable;

        ID NAME

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

        10 ab

        10 ab,bc

        10 ab,bc,cd

        20 ab,bc,cd,hi

        20 ab,bc,cd,hi,ij

        20 ab,bc,cd,hi,ij,mn

6 rows selected

个人觉得这个用法比较有趣.

SQL> select id,wmsys.wm_concat(name) over (partition by id) name from idtable;

        ID NAME

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

        10 ab,bc,cd

        10 ab,bc,cd

        10 ab,bc,cd

        20 hi,ij,mn

        20 hi,ij,mn

        20 hi,ij,mn

6 rows selected
----------------------------------------------------------

 select id,

        fid,

        bmmc,

        jgbh,

        SYS_CONNECT_BY_PATH(to_char(sxh, '00'), '.') sort

   from gg_bmxx

  where --gg_bmxx.jgbh = '001'

  gg_bmxx.fid = '4028826b38a2e4fc0138a337e68e001c'

  start with fid is null

 connect by prior gg_bmxx.id = gg_bmxx.fid

  order by sort, sxh

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

select id,
        fid,
        bmmc,
        jgbh,
        SYS_CONNECT_BY_PATH(to_char(sxh, '00'), '.') sort
   from gg_bmxx
  where gg_bmxx.jgbh = '001'
  start with fid = '4028826b38a2e4fc0138a337e68e001f'
 connect by prior gg_bmxx.id = gg_bmxx.fid
  order by sort, sxh;

如若某个表中存有字段类似(x,y,z),现将（x,y,z）查询出来对应的内容（m,l,n）.

select WMSYS.WM_CONCAT(g.dmmc) as lbstr
 from gg_sjzd g
 where length(g.id)>4
 and instr(
(select t.gslb from jc_czy t where t.id=?),g.id)>0

------------------x,y,z->m,l,n-----------适用于单个记录查询
SELECT WMSYS.WM_CONCAT(DLJGJG.DLJGMC) zjmc
  FROM JD_DLJGJG DLJGJG, JD_CGDLJG CGDLJG
 WHERE DLJGJG.DLJGBH = CGDLJG.ID
   AND DLJGJG.CQGZBH = ?

----相同的标识下的信息拼接如：某班级的若干学生名称：张三，李四，王五（适用单个记录查询）---