求一个聚合功能sql语句
有一个表tableA,结构如下：
col1 col2 col3 col4
A 1 a 232
A 2 b 1212
A 3 c 656
B 1 e er
B 2 f 323
查询后的结果期望如下：
即根据一个相同的字段，来组合成一条记录(用逗号隔开)
A 1,2,3 a,b,c 232,1212,656
B 1,2 e,f er,323
同时还有另一个表tableT，该表描述了tableA中记录的详细其他信息，
即最后的查询结果期望是：
A Ta1 Ta2 Ta3 1,2,3 a,b,c 232,1212,656
B Tb1 Tb2 Tb3 1,2 e,f er,323
一共7个字段！
求该sql。
最新回复
zcc@weichai.com (2008-11-25 07:45:35)
gavinhuang (2008-11-25 09:13:12)
mengyong (2008-11-25 16:54:23)
as
object
(
str varchar2(30000),
static function odciaggregateinitialize (sctx in out t_link) return number,
member function odciaggregateiterate (self in out t_link, value in varchar2) return number,
member function odciaggregateterminate (self in t_link, returnvalue out varchar2, flags in number) return number,
member function odciaggregatemerge (self in out t_link, ctx2 in t_link) return number
);
/
create or replace type body t_link is
static function odciaggregateinitialize (sctx in out t_link) return number
is
begin
sctx := t_link(null);
return odciconst.success;
end;
member function odciaggregateiterate (self in out t_link, value in varchar2) return number
is
begin
self.str := self.str || ','|| value;
return odciconst.success;
end;
member function odciaggregateterminate (self in t_link, returnvalue out varchar2, flags in number) return number
is
begin
returnvalue := self.str;
return odciconst.success;
end;
member function odciaggregatemerge (self in out t_link, ctx2 in t_link) return number
is
begin
null;
return odciconst.success;
end;
end;
/
create or replace function f_link (p_str varchar2) return varchar2
parallel_enable aggregate using t_link;
/
select a,f_link(c) name
from
(
select c.a a,b,c from class c
join classsub cs
on c.a=cs.a
join subject s
on cs.b=s.b
)
group by a;
jack913.fan (2008-11-25 20:31:33)
SQL> with a as (select 'A' col1,1 col2,'a' col3,232 col4 from dual
2 union
3 select 'A' col1,2 col2,'b' col3,1212 col4 from dual
4 union
5 select 'A' col1,3 col2,'c' col3,565 col4 from dual
6 union
7 select 'B' col1,1 col2,'e' col3,457 col4 from dual
8 union
9 select 'B' col1,2 col2,'f' col3,23 col4 from dual
10 )
11 select col1,wmsys.wm_concat(col2) col2,wmsys.wm_concat(col3) col3,wmsys.wm_concat(col4) col4 from a
12 group by col1
13 ;
COL1 COL2 COL3 COL4
---- --------------------- ---------------------- --------------------------------------------------------------------------------
A 1,2,3 a,b,c 232,1212,565
B 1,2 e,f 457,23
SQL>
ORACLE 9I的写法
SQL> with a as (select 'A' col1,1 col2,'a' col3,232 col4 from dual
2 union
3 select 'A' col1,2 col2,'b' col3,1212 col4 from dual
4 union
5 select 'A' col1,3 col2,'c' col3,565 col4 from dual
6 union
7 select 'B' col1,1 col2,'e' col3,457 col4 from dual
8 union
9 select 'B' col1,2 col2,'f' col3,23 col4 from dual
10 )
11 select col1, max(substr(sys_connect_by_path(col2, ','),2)) col2,
12 max(substr(sys_connect_by_path(col3, ','),2)) col3,
13 max(substr(sys_connect_by_path(col4, ','),2)) col4
14 from (select a.*,
15 row_number() over(partition by col1 order by col1, col2) rn
16 from a)
17 start with rn = 1
18 connect by rn - 1 = prior rn and col1=prior col1
19 group by col1
20 /
COL1 COL2 COL3 COL4
---- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
A 1,2,3 a,b,c 232,1212,565
B 1,2 e,f 457,23
SQL>
jack913.fan (2008-11-25 20:47:56)
COL1 COL2 COL3 COL4
---- ---------- ---- ----------
A 1 a 232
A 2 b 1212
A 3 c 565
B 1 e 457
B 2 f 23
SQL> select a.col1, max(decode(b.column_name,'COL2','T'||a.col1||'1')) a1,
2 max(decode(b.column_name,'COL3','T'||a.col1||'1')) a2,
3 max(decode(b.column_name,'COL4','T'||a.col1||'1')) a3,
4 max(a.col2) col2,max(a.col3) col3,max(a.col4) col4
5 from (select col1,wmsys.wm_concat(col2) col2,wmsys.wm_concat(col3) col3,wmsys.wm_concat(col4) col4
6 from a1
7 group by col1) a, (select column_name from all_col_comments where owner='BM' and table_name='A1') b
8 group by a.col1
9 /
COL1 A1 A2 A3 COL2 COL3 COL4
---- --- --- --- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
A TA1 TA1 TA1 1,2,3 a,b,c 232,1212,565
B TB1 TB1 TB1 1,2 e,f 457,23
SQL>
wzion (2008-11-27 19:43:05)