一个多级菜单树，如何删除一个父类下的全部子类
一个多级菜单树，父级和子级的关系是 父级的ID = 子级的PID
同理子级的ID = 孙级的PID 类推下去。有多少级不确定。
如果删除一个父级，则其下的所有子级，子级下面的N级都一起删除。
页面选择父级传到后台的父级的ID 就问下这样的删除语句该怎么写？ 是不是要用循环语句？
本人新手，请说详细点
[解决办法]
BOM按节点排序应用实例
--------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-23 02:37:28
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Subject: BOM按节点排序应用实例
--------------------------------------
--实例1:
--> 生成测试数据表:tb
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[code] NVARCHAR(10),[pid] INT,[name] NVARCHAR(10))
INSERT [tb]
SELECT 1,'01',0,N'服装' UNION ALL
SELECT 2,'01',1,N'男装' UNION ALL
SELECT 3,'01',2,N'西装' UNION ALL
SELECT 4,'01',3,N'全毛' UNION ALL
SELECT 5,'02',3,N'化纤' UNION ALL
SELECT 6,'02',2,N'休闲装' UNION ALL
SELECT 7,'02',1,N'女装' UNION ALL
SELECT 8,'01',7,N'套装' UNION ALL
SELECT 9,'02',7,N'职业装' UNION ALL
SELECT 10,'03',7,N'休闲装' UNION ALL
SELECT 11,'04',7,N'西装' UNION ALL
SELECT 12,'01',11,N'全毛' UNION ALL
SELECT 13,'02',11,N'化纤' UNION ALL
SELECT 14,'05',7,N'休闲装'
GO
--SELECT * FROM [tb]
-->SQL查询如下:
;WITH T AS
(
SELECT CAST(CODE AS VARCHAR(20)) AS CODE,*,
CAST(ID AS VARBINARY(MAX)) AS px
FROM tb AS A
WHERE NOT EXISTS(SELECT * FROM tb WHERE id=A.pid)
UNION ALL
SELECT CAST(B.CODE+A.CODE AS VARCHAR(20)),A.*,
CAST(B.px+CAST(A.ID AS VARBINARY) AS VARBINARY(MAX))
FROM tb AS A
JOIN T AS B
ON A.pid=B.id
)
SELECT Code,Name FROM T
ORDER BY px
/*
Code Name
-------------------- ----------
01 服装
0101 男装
010101 西装
01010101 全毛
01010102 化纤
010102 休闲装
0102 女装
010201 套装
010202 职业装
010203 休闲装
010204 西装
01020401 全毛
01020402 化纤
010205 休闲装
(14 行受影响)
*/
--实例2:
--> 生成测试数据表:tb
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[parentid] INT,[categoryname] NVARCHAR(10))
INSERT [tb]
SELECT 1,0,'test1' UNION ALL
SELECT 2,0,'test2' UNION ALL
SELECT 3,1,'test1.1' UNION ALL
SELECT 4,2,'test2.1' UNION ALL
SELECT 5,3,'test1.1.1' UNION ALL
SELECT 6,1,'test1.2'
GO
--SELECT * FROM [tb]
-->SQL查询如下:
;WITH T AS
(
SELECT *,CAST(ID AS VARBINARY(MAX)) AS px
FROM tb AS A
WHERE NOT EXISTS(SELECT * FROM tb WHERE id=A.[parentid])
UNION ALL
SELECT A.*,CAST(B.px+CAST(A.ID AS VARBINARY) AS VARBINARY(MAX))
FROM tb AS A
JOIN T AS B
ON A.[parentid]=B.id
)
SELECT [id],[parentid],[categoryname] FROM T
ORDER BY px
/*
id parentid categoryname
----------- ----------- ------------
1 0 test1
3 1 test1.1
5 3 test1.1.1
6 1 test1.2
2 0 test2
4 2 test2.1
(6 行受影响)
*/
--楼至可以参考一下
[解决办法]
方法很多：
1. 有多少级数就得写几层嵌套才能查询子节点，然后递归删除
2. 删除结点后，判断子节点的上级PID不存在的删除
最好的办法：
３．设计上调整一下就可以了，方法是采用分级编码，下一级的ID是上一级ID+几位序号，这样就很简单可以用Left(ID,4)='XXXX'删除了。
