数据库中一条删除语句的问题
用数据库保存一棵树的信息
如何运用存储过程删除节点下的所有子节点
表结构只有最基本的
id 父id
[解决办法]

USE test
GO


-->生成表tb

if object_id('tb') is not null 
drop table tb
Go
Create table tb([id] smallint,[parentid] nvarchar(1))
Insert into tb
Select 1,N'0'
Union all Select 2,N'1'
Union all Select 3,N'1'
Union all Select 4,N'2'
Union all Select 5,N'2'
Union all Select 6,N'3'


SELECT * FROM tb
/*
id     parentid
------ --------
1      0
2      1
3      1
4      2
5      2
6      3
*/

;WITH t AS (
SELECT * FROM tb
WHERE id=2
UNION ALL
SELECT a.* FROM tb AS a
INNER JOIN t ON a.parentid=t.id
)DELETE a FROM t INNER JOIN tb AS a ON t.id=a.id

/*
id     parentid
------ --------
2   1
4   2
5   2
*/

SELECT * FROM tb

/*
id     parentid
------ --------
1      0
3      1
6      3

*/