17,086
社区成员
发帖
与我相关
我的任务
分享
--那就把所有数据都颠倒过来就好了,这样可以选择你要的数据就可以 了
select a.pid,a.id
from (--对原来的字段全部做颠倒顺序,这样就可以了
select a.pid as id,a.id as pid,
from a
union all
select a.id as id,a.pid as pid,
from a
) a
start with a.pid=1
connect by a.pid=prior a.id
create table t AS(
SELECT 1 id1,2 id2 FROM dual
UNION ALL SELECT 2,3 FROM dual
UNION ALL SELECT 4,3 FROM dual
UNION ALL SELECT 4,5 FROM dual
UNION ALL SELECT 6,5 FROM dual
UNION ALL SELECT 7,6 FROM dual);
DECLARE
start_value NUMBER:=1;
v_pid NUMBER;
v_id NUMBER;
v_count number;
BEGIN
SELECT count(1) INTO v_count FROM user_tables WHERE table_name='TMP_1';
if v_count=0 then
EXECUTE IMMEDIATE 'create global temporary table tmp_1(pid number,id number) on commit preserve rows';
ELSE EXECUTE IMMEDIATE 'truncate table tmp_1';
end if;
v_id:=start_value;
loop
SELECT decode(id1,v_id,id2,id1),decode(id1,v_id,id1,id2) INTO v_id,v_pid FROM t
WHERE v_id IN(id1,id2) and (v_pid not in(id1,id2) or v_pid is null);
EXECUTE IMMEDIATE 'insert into tmp_1 values(:1,:2)' USING v_pid,v_id;
end loop;
exception
WHEN no_data_found THEN
commit;
end;
/
select * from tmp_1;
PID ID
---------------------- ----------------------
1 2
2 3
3 4
4 5
5 6
6 7
6 所选行
select a.pid,a.id
from (--想通了,,,对原来的字段顺序做下调整.把数据小的放到前面,这样就可以了
select case when a.pid-a.id>0 then a.id else a.pid end as id,
case when a.pid-a.id then a.pid else a.id end as pid from a
) a
start with a.pid=1
connect by a.pid=prior a.id
select a.pid,a.id
from (--想通了,,,对原来的字段顺序做下调整.
select case when a.pid-a.id>0 then a.id else a.pid end as id,
case when a.id-a.age>0 then a.pid else a.id end as pid from a
) a
start with a.pid=1
connect by a.pid=prior a.id
select a.pid,a.id
from a
start with a.pid=1
connect by a.pid=prior a.id