日期:2014-05-17 浏览次数:20439 次
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
*/