树的问题

有点难度，想想。。。

用connect by start with可以实现，具体的我说不清楚，你查资料吧

找到ID='XXX'的所有子记录：
select * from your_table start with id='xxxx'
connect by fid=prior id

删除：
delete from your_table where id in
(select id from your_table start with id='xxxx'
connect by fid=prior id)

如果表是自引用的，这样还删除不掉,提示“已找到子记录”。
用过程肯定可以。

from c_id in (select id from --对记录排序，从底层记录开始删除
(
select rn,id from
(
(select rownum rn,id from your_table start with id='xxx'
connect by fid=prior id)
)order by rn desc
)
loop

delete from your_table where id=c_id.id;
end loop;

我的数据库是mysql有没有别的办法

zhangshunshi(宇轩) ：
这样只把第一层的字节点删除了，树形结构的话不一定只有两层，子节点下面的那些节点也要删除吧

不知道MYSQL中是否可以定义级联删除,下面是ORACLE下的例子:

SQL> create table a (id varchar2(10),pid varchar2(10));

Table created.

SQL> alter table a add constraint pk_a primary key (id);

Table altered.

SQL> alter table a add constraint fk_a
2 foreign key (pid) references a(id)
3 on delete cascade;

Table altered.

SQL> select * from a;

no rows selected

SQL> insert into a values ('1',null);

1 row created.

SQL> insert into a values ('2','1');

1 row created.

SQL> insert into a values ('3','1');

1 row created.

SQL> insert into a values ('4',null);

1 row created.

SQL> insert into a values ('5','3');

1 row created.

SQL> select * from a;

ID PID
---------- ----------
1
2 1
3 1
4
5 3

SQL> delete from a where id=1;

1 row deleted.

SQL> select * from a;

ID PID
---------- ----------
4