有表如下: id1 id2
1 2
2 3
4 3
4 5
6 5
7 6已知:拓扑是树结构,id1和id2表示拓扑中两个设备id,表中每行表示两个设备有连接关系。且知道id为1的设备是根节点。
求:通过上表得到如下表(pid为id的父节点)
pid id
1 2
2 3
3 4
4 5
5 6
6 7
请高手赐教,不胜感激!
1 2
2 3
4 3
4 5
6 5
7 6已知:拓扑是树结构,id1和id2表示拓扑中两个设备id,表中每行表示两个设备有连接关系。且知道id为1的设备是根节点。
求:通过上表得到如下表(pid为id的父节点)
pid id
1 2
2 3
3 4
4 5
5 6
6 7
请高手赐教,不胜感激!
from a
start with a.pid=1
connect by a.pid=prior 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
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 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
connect by后再加个条件就行了SELECT *
FROM (select id1,id2 from t union all select id2,id1 from t)
START WITH id1=1
CONNECT BY id1=PRIOR id2
and id2<>prior id1
来62697716群吧``