[php] --------- 在with在oracle中称为家族树!pub中oracle模块对此类的使用已经很成熟! 因为有人问及,这边简单的再探讨一下 ------------------- 1.创建测试表 create table zxt_test ( id varchar(10), ivalue varchar(20), iname varchar(20) ) not logged initially; commit; ----------- 2.插入测试语句 insert into zxt_test values('1','aa','x'),('2','bb','x'),('3','bb','x'),('1','bb','y'),('2','bb','y'),('3','bb','y'); commit; --------------- 3.查看数据 select * from zxt_test; 1 'aa' 'x' 2 'bb' 'x' 3 'bb' 'x' 1 'bb' 'y' 2 'bb' 'y' 3 'bb' 'y' ---------------- 4.with 的写法! with s as ( select row_number()over(partition by iname order by id) id1, row_number()over(partition by iname order by id) id2, ivalue,iname from zxt_test ) , t(iname,id1,id2,ivalue) as ( select iname,id1,id2,cast(ivalue as varchar(100)) from s where id1 =1 and id2=1 union all select t.iname,t.id1+1,t.id2,cast(s.ivalue||','||t.ivalue as varchar(100)) from s, t where s.id2=t.id1+1 and t.iname = s.iname ) select iname,ivalue from t where t.id1= (select max(id1) from s where s.iname = t.iname); 5.结果如下: 'x' 'bb,bb,aa' 'y' 'bb,bb,bb' |