把一个表中的数据,时间不重复,rid保存到一起,合并的多个rid可以用,或其他符号隔开,效果如下,请高手指点tab1表有2个字段rid,date。内容如下: rid date
1 2009-01-02
2 2009-01-02
3 2009-01-03
5 2009-01-02
7 2009-01-03得到的结果
rid date
1,2,5 2009-01-02
3,7 2009-01-03
1 2009-01-02
2 2009-01-02
3 2009-01-03
5 2009-01-02
7 2009-01-03得到的结果
rid date
1,2,5 2009-01-02
3,7 2009-01-03
, ltrim(sys_connect_by_path(rid,','),',') rids
from ( select date
, rid
, row_number() over(partition by date order by rid) rn
, count(1) over(partition by date) cnt
from tab1
)
where level = cnt
start with rn = 1
connect by prior date = date
and prior rn + 1 = rn;
create or replace type vcArray as table of varchar2(4000);2. 第二步
create or replace type string_agg_type as object
(
data vcArray, static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
return number, member function ODCIAggregateIterate(self IN OUT string_agg_type,
value IN varchar2) return number, member function ODCIAggregateTerminate(self IN string_agg_type,
returnValue OUT varchar2,
flags IN number)
return number, member function ODCIAggregateMerge(self IN OUT string_agg_type,
ctx2 IN string_agg_type) return number
)
;3. 第三步
create or replace type body string_agg_type is static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
return number is
begin
sctx := string_agg_type(vcArray());
return ODCIConst.Success;
end; member function ODCIAggregateIterate(self IN OUT string_agg_type,
value IN varchar2) return number is
begin
data.extend;
data(data.count) := value;
return ODCIConst.Success;
end; member function ODCIAggregateTerminate(self IN string_agg_type,
returnValue OUT varchar2,
flags IN number) return number is
l_data varchar2(4000);
begin
for x in (select column_value from TABLE(data) order by 1) loop
l_data := l_data || ',' || x.column_value;
end loop;
returnValue := ltrim(l_data, ',');
return ODCIConst.Success;
end; member function ODCIAggregateMerge(self IN OUT string_agg_type,
ctx2 IN string_agg_type) return number is
begin
-- not really tested ;)
for i in 1 .. ctx2.data.count loop
data.extend;
data(data.count) := ctx2.data(i);
end loop;
return ODCIConst.Success;
end;
end;4. 第四步
CREATE or replace FUNCTION stragg(input varchar2) RETURN varchar2
PARALLEL_ENABLE
AGGREGATE USING string_agg_type;
5. 以后一直这个方法就行了,举例:
select deptno, stragg(ename) ename
from emp
group by deptno结果:
DEPTNO ENAME
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
select date,
ltrim(max(sys_connect_by_path(rid,',')),',') rids
from ( select date,
rid,
row_number() over(partition by date order by rid) rn,
from tab1
)
start with rn = 1
connect by prior date = date
and prior rn - 1 = rn;