sql求解

[8a1b2c]

表test 字段
a1 a2
1 a
1 b
2 x
2 y
2 z


查询的结果要求

1 ab
2 xyz

[mantisXF]

FYI:

http://topic.csdn.net/u/20071114/08/0227d13b-b8b0-420c-a7bc-b46fb4bfc722.html

[晓风残月0110]

分组之后把相同组相加
想使用函数把一组的数据相加，
之后使用select 的到你要的结果
pl-sql不熟

[wdswcy]

select   max(substr((sys_connect_by_path(a2,'')),2))   cola 
  from   ( 
  select   a1,a2, 
                rownum   rnum, 
                row_number()   over(partition   by   a1  order   by   a1)   rn1 
  from   a 
  ) 
  start   with   rn1=1 
  connect   by   rnum-1=prior   rnum 
  group   by   a1 

[fxianxian]

[code=SQL]SQL> with b as (select 1 a1,'a' a2 from dual
  2             union
  3             select 1 a1,'b' a2 from dual
  4             union
  5             select 2 a1,'x' a2 from dual
  6             union
  7             select 2 a1,'y' a2 from dual
  8             union
  9             select 2 a1,'z' a2 from dual
 10            )
 11  select a1,max(sys_connect_by_path(a2,' ')) aa from
 12  (select a1,a2,rownum rnum,row_number()over(partition by a1 order by a1,a2) rn from b)
 13  start with rn=1
 14  connect by rnum-1=prior rnum
 15  group by a1
 16  ;
 
        A1 AA
---------- --------------------------------------------------------------------------------
         1  a b
         2  x y z

[/code]