A1 1 01A1 1 02A1 1 03A2 1 01A2 ...
显示全部A1 1 01
A1 1 02
A1 1 03
A2 1 01
A2 2 01
A2 2 02
-- drop table test1 ;
-- create table test1
-- (
-- t1 varchar(100),
-- t2 int,
-- t3 varchar(100)
-- ) ;
--
-- insert into test1
-- values
-- ('A1', 1 ,'01'),('A1' ,1, '02'),('A1', 1, '03'),
-- ('A2', 1, '01'),('A2', 2, '01'),('A2', 2 ,'02') ;
with x (t1,t2,cnt,list,t3,len)
as (select t1,t2,count(*) over (partition by t1,t2),cast(t3 as varchar(100)),t3,1 from test1
union all
select x.t1,x.t2,x.cnt,x.list||','||e.t3,e.t3,x.len+1
from test1 e,x where e.t1=x.t1 and e.t2=x.t2 and e.t3>x.t3
)
select t1,t2,list from x where len=cnt;
-- create table recursion
-- (
-- id int ,
-- action varchar(100)
-- );
--
-- delete from recursion ;
-- insert into recursion values(1,'a');
-- insert into recursion values(1,'b');
-- insert into recursion values(2,'c');
-- insert into recursion values(2,'d');
-- insert into recursion values(1,'e');
-- insert into recursion values(2,'f');
with x(id,action,t1,t2) as
(
select id,action ,t1,t2 from (select id ,action ,rownumber()over(partition by id) t1 ,rownumber()over(partition by id) t2 from recursion ) a
where t1=1 and t2=1
union all
select x.id,x.action||'-'||b.action,x.t1+1,x.t2
from x , (select id ,action ,rownumber()over(partition by id) t1 ,rownumber()over(partition by id) t2 from recursion ) b where b.id=x.id and x.t1=b.t2-1
)
select * from x where t1=3;
收起