例如:
select base_id, translate (ltrim (text1, '/'), '*/', '*,') xmmc,translate (ltrim (text2, '/'), '*/', '*,') xmid FROM (SELECT ROW_NUMBER () OVER (PARTITION BY base_id ORDER BY base_id, lvl DESC) rn, base_id, text1,text2 FROM ( SELECT base_id, LEVEL lvl, sys_connect_by_path (info_name, '/') text1,sys_connect_by_path (info_id, '/') text2 FROM ( SELECT base_id, info_name,info_id, ROW_NUMBER () OVER (PARTITION BY base_id ORDER BY base_id, info_name) x FROM base_relation ORDER BY base_id, info_name) a CONNECT BY base_id = PRIOR base_id = PRIOR x)) ORDER BY base_id;
查询结果:
SQL分析:
1、利用 “ROW_NUMBER () OVER (PARTITION BY……” 为按“base_id”汇总后的数据行添加组内序号
2、“SYS_CONNECT_BY_PATH” 按组内序号相邻关系,为每一层进行不同行的“Info_id”叠加
3、再次利用“base_id”进行组内分组,但按第二部中的层次排倒序,增加调整后等级
4、取所有调整后等级为1的结果,即为所要求的数据行.