with a as (select 1 id,'s' name,100 fee,'200711' month from dual
union all
select 1 id,'s' name,200 fee,'200710' month from dual
union all
select 1 id,'s' name,300 fee,'200709' month from dual
union all
select 2 id,'a' name,100 fee,'200711' month from dual
union all
select 2 id,'a' name,200 fee,'200710' month from dual
union all
select 2 id,'a' name,3000 fee,'200709' month from dual
union all
select 3 id,'m' name,1000 fee,'200709' month from dual
)
select id,name,max(decode(rn,1,month,null)) month1,
max(decode(rn,1,fee,null)) fee1,
max(decode(rn,2,month,null)) month2,
max(decode(rn,2,fee,null)) fee2,
max(decode(rn,3,month,null)) month3,
max(decode(rn,3,fee,null)) fee3
from (select id,name,month,fee,row_number()over(partition by id order by month) rn from a)
group by id,name
order by id
/
最简单的方法
with testa as (select 'a' || rownum q, 'a' no
from dual
connect by rownum < 4
union
select 'b' || rownum name, 'c' rn
from dual
connect by rownum <2
union
select 'c' || rownum name, 'd' rn
from dual
connect by rownum <5
)
select no,max(sys_connect_by_path(q,',')) from (
select no,q,
row_number() over( order by no) rn,
row_number() over(partition by no order by no) rn1
from testa
)
start with rn1=1
connect by rn-1=prior rn
group by no
第二种方法错误
实现第三种方法
with a as (
select trunc(dbms_random.value(1,20)) no, trunc(dbms_random.value(1,20)) q from dual
connect by rownum < 40
)
select a.no,to_char(q) from a
union all
select no,max(sys_connect_by_path(q,',')) from ( select l.no,l.q,l.rn+r.rn rn,rn1
from (
select no,q,
row_number() over(order by no) rn,
row_number() over(partition by no order by no) rn1
from a
) l,
(select no,rownum rn from (
select distinct no
from a
order by no
) ) r
where l.no=r.no
)
start with rn1=1
connect by rn-1=prior rn
group by no
10g以上的方法简单
WITH A AS (
SELECT 1123 ID,'2008-9-19' DAY,'9:7:47' TIME FROM DUAL UNION
SELECT 1123 ID,'2008-9-19' DAY,'19:7:47' TIME FROM DUAL UNION
SELECT 1123 ID,'2008-9-19' DAY,'13:7:47' TIME FROM DUAL UNION
SELECT 1123 ID,'2008-9-19' DAY,'12:7:47' TIME FROM DUAL UNION
SELECT 1123 ID,'2008-19-19' DAY,'12:7:47' TIME FROM DUAL
)
select ID,DAY,WMSYS.WM_CONCAT(TIME) TIME FROM A GROUP BY ID,DAY
10g以下
WITH A AS (
SELECT 1123 ID,'2008-9-19' DAY,'9:7:47' TIME FROM DUAL UNION
SELECT 1123 ID,'2008-9-19' DAY,'19:7:47' TIME FROM DUAL UNION
SELECT 1123 ID,'2008-9-19' DAY,'13:7:47' TIME FROM DUAL UNION
SELECT 1123 ID,'2008-9-19' DAY,'12:7:47' TIME FROM DUAL UNION
SELECT 1123 ID,'2008-19-19' DAY,'12:7:47' TIME FROM DUAL
)
select ID,DAY,MAX(SYS_CONNECT_BY_PATH(TIME,',')) TIME FROM
(SELECT ID,DAY,TIME,ROW_NUMBER()OVER(PARTITION BY ID,DAY ORDER BY ID ) RN FROM A)
START WITH RN=1
CONNECT BY RN-1=PRIOR RN
GROUP BY ID,DAY
8i写函数来实现
create or replace package hierarchy is
type strtabletype is table of varchar2(4000) index by binary_integer;
strtable strtabletype;
type numtabletype is table of number index by binary_integer;
numtable numtabletype;
function sys_connect_by_path(p_level in number,
p_value in varchar2,
p_delimiter in varchar2 default ',')
return varchar2;
function sys_sum_by_path(p_level in number, p_value in number)
return number;
pragma restrict_references(sys_connect_by_path, wnds);
pragma restrict_references(sys_sum_by_path, wnds);
end;
/
create or replace package body hierarchy is
ls_ret varchar2(4000);
ln_ret number;
function sys_connect_by_path(p_level in number,
p_value in varchar2,
p_delimiter in varchar2 default ',')
return varchar2 is
begin
strtable(p_level) := p_value;
ls_ret := p_value;
for i in reverse 1 .. p_level - 1 loop
ls_ret := strtable(i) || p_delimiter || ls_ret;
end loop;
return ls_ret;
end;
function sys_sum_by_path(p_level in number, p_value in number)
return number is
begin
numtable(p_level) := p_value;
ln_ret := p_value;
for i in reverse 1 .. p_level - 1 loop
ln_ret := numtable(i) + ln_ret;
end loop;
return ln_ret;
end;
end;
/
select rpad(' ', 4 * level, ' ') || ename emp_name,
sal,
hierarchy.sys_connect_by_path(level, sal, '/') sal_path,
hierarchy.sys_sum_by_path(level, sal) sal_sum
from emp
start with mgr is null
connect by prior empno = mgr;
转自http://www.cnblogs.com/gkl0818/archive/2009/03/01/1401039.html