有人知道怎么实现下面的SQL吗?(DB2环境下)
本帖最后由 sunsunzhang 于 2010-02-23 14:58 编辑知识id 行为名称
1 *
1 &&
2 %
2 ##
需要输出为：
1 *&&
2 %## select a.知识id, a.行为名称 +b.行为名称
from tbl a,tbl b
where a.知识id = b.知识id
and a.rownum <> b.rownum -- 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 fromrecursion ) 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 fromrecursion ) bwhere b.id=x.id and x.t1=b.t2-1
)
select*from xwhere t1=3;
where t1=3; 这块的3可以搞个子查询。你自己写吧
页:
[1]