select 类型, sum(值),replace(wm_concat(名称),',','、') from tab group by 类型
select 类型,sum(值),replace(wm_concat(名称),',','、') from tb group by 类型
select 类型,sum(值),wm_concat(名称) from tb group by 类型
--------9i版本 SQL> SQL> with a as( 2 select 'Y' type,100 value,'A帐号' name from dual union all 3 select 'Y' type,200 value,'B帐号' name from dual union all 4 select 'Y' type,150 value,'C帐号' name from dual 5 ) 6 SELECT t.type type, 7 sum(value), 8 MAX(substr(sys_connect_by_path(t.name, '、'), 2)) str 9 FROM (SELECT type, 10 value, 11 name, 12 row_number() over(PARTITION BY type ORDER BY name) rn 13 FROM a) t 14 START WITH rn = 1 15 CONNECT BY rn = PRIOR rn + 1 16 AND type = PRIOR type 17 GROUP BY t.type;TYPE SUM(VALUE) STR ---- ---------- -------------------------------------------------------------------------------- Y 450 A帐号、B帐号、C帐号SQL>
select 类型,sum(值),replace(wm_concat(名称),',','、') from tb group by 类型
select 类型,sum(值),wm_concat(名称)
from tb group by 类型
--------9i版本
SQL>
SQL> with a as(
2 select 'Y' type,100 value,'A帐号' name from dual union all
3 select 'Y' type,200 value,'B帐号' name from dual union all
4 select 'Y' type,150 value,'C帐号' name from dual
5 )
6 SELECT t.type type,
7 sum(value),
8 MAX(substr(sys_connect_by_path(t.name, '、'), 2)) str
9 FROM (SELECT type,
10 value,
11 name,
12 row_number() over(PARTITION BY type ORDER BY name) rn
13 FROM a) t
14 START WITH rn = 1
15 CONNECT BY rn = PRIOR rn + 1
16 AND type = PRIOR type
17 GROUP BY t.type;TYPE SUM(VALUE) STR
---- ---------- --------------------------------------------------------------------------------
Y 450 A帐号、B帐号、C帐号SQL>