oracle 10,这个方法可能比较笨(函数/游标):
-------------------------------------------------------------------
select a.MobileNum,count(a.MobileNum) "Count",
substr(f1(a.MobileNum),2,length(f1(a.MobileNum))) Newaddress
from Table1 a
group by a.MobileNum;
-------------------------------------------------------------------
----其中,自定义函数f1如下:
CREATE OR REPLACE function f1
(
MobNum in varchar
)
return varchar
is
reVal varchar(500);
Val varchar(20); cursor cr1 is
select trim(address)
from Table1
where MobileNum = MobNum;begin
reVal := '';
open cr1; FETCH cr1 INTO Val;
while cr1%FOUND
LOOP
reVal := reVal||'/'||Val;
FETCH cr1 INTO Val;
END LOOP; close cr1; return(reVal);
end f1;
/
----自定义函数结束.
-------------------------------------------------------------------
select a.MobileNum,count(a.MobileNum) "Count",
substr(f1(a.MobileNum),2,length(f1(a.MobileNum))) Newaddress
from Table1 a
group by a.MobileNum;
-------------------------------------------------------------------
----其中,自定义函数f1如下:
CREATE OR REPLACE function f1
(
MobNum in varchar
)
return varchar
is
reVal varchar(500);
Val varchar(20); cursor cr1 is
select trim(address)
from Table1
where MobileNum = MobNum;begin
reVal := '';
open cr1; FETCH cr1 INTO Val;
while cr1%FOUND
LOOP
reVal := reVal||'/'||Val;
FETCH cr1 INTO Val;
END LOOP; close cr1; return(reVal);
end f1;
/
----自定义函数结束.
from
(select t.MobileNum,t.xm,t.Newaddress,rownum rn From Table1 t)
start with rn=1
connect by prior rn=rn-1
Group By nsrnm
from
(select t.MobileNum,t.xm,t.Newaddress,rownum rn From Table1 t)
start with rn=1
connect by prior rn=rn-1
Group By MobileNum