http://forums.mysql.com/read.php?108,52262,52262#msg-52262有人回答了他如下:
Gert-Jan, A possible solution is to use a recursively called SP passing in the son number of the current record. You could select it's son and build up a list of concatenated numbers. When the SP finally returns you'd have a string that you can then tear apart to get the numbers. Maybe that's an idea to consider?但其中的SP我看不懂,SP指的是什么单词的缩写啊?斗胆问一下前辈们,有人做过类似的sql查询吗?
我想听听你们的想法,我想用Oralce和mysql实现.
谢谢了!!
Gert-Jan, A possible solution is to use a recursively called SP passing in the son number of the current record. You could select it's son and build up a list of concatenated numbers. When the SP finally returns you'd have a string that you can then tear apart to get the numbers. Maybe that's an idea to consider?但其中的SP我看不懂,SP指的是什么单词的缩写啊?斗胆问一下前辈们,有人做过类似的sql查询吗?
我想听听你们的想法,我想用Oralce和mysql实现.
谢谢了!!
1,使用 start with /connect by
2,专用函数sys_connect_by_path
树:connect by的使用
举例:
table:root
1103node1node2
2104node2node3
3105node3node5
4106node4node5
5107node5rootselect * from node start with node='node4' connect by prior pnode=node
--从子节点到根节点查询(从下到上)
1106node4node5
2107node5root
select * from node start with node='node4' connect by prior node=pnode
--从上到下
1106node4node5查树的专用函数sys_connect_by_path
1 eric null (所有人的父亲)
2 jack 1 (eric的儿子)
3 sam 2 (jack的儿子)
4 bob 2 (jack的儿子)
5 gatt 3 (sam的儿子)
6 john 5 (gatt的儿子)我如果要得到jack的所有子孙该如何些sql??还有个问题。
树状查询
1.使用 start with /connect by
2.专用函数sys_connect_by_path
是不是mysql不能实现,我是不是要装oracle,oracle9i能不能胜任??
start with id='1'
connect by PRIOR id=father's id
比如:从sam开始,是不是要分两句写,一句从sam往上搜,一句从sam往下.
我想得到sam的整个家族的表,能不能用一句sql来搞定呢??各为老大,谢谢啊!!
insert into relationship(id,name,fatherid) values(2,'jack',1);
insert into relationship(id,name,fatherid) values(3,'sam',2);
insert into relationship(id,name,fatherid) values(4,'bob',2) ;
insert into relationship(id,name,fatherid) values(5,'gatt',3);
insert into relationship(id,name,fatherid) values(6,'john',5) ;
select id,name,fatherid from relationship
start with name='john'
connect by PRIOR id=fatherid
union
select id,name,fatherid from relationship
start with name='john'
connect by PRIOR fatherid=id
select id,sys_connect_by_path(name,'->'),fatherid from relationship
start with name='sam'
connect by PRIOR id=fatherid
union
select id,sys_connect_by_path(name,'->'),fatherid from relationship
start with name='sam'
connect by PRIOR fatherid=id
输出结果:
ID name fatherid
1 ->sam->jack->eric
2 ->sam->jack 1
3 ->sam 2
5 ->sam->gatt 3
6 ->sam->gatt->john 5