高手帮忙：sql 里怎样递归判断父节点和子节点的关系？（附图和详细说明）
如题，如下表，tdma01是目录,tdma03是上层目录名:
比如word_test_test的上层目录是DMA20120317004(word_test),DMA20120317004的上层目录是DMA20120313004(word),DMA20120313004的上层目录是DMA20120313001(office重要文件，其中root是根目录。
如图：
[img=http://b101.photo.store.qq.com/psb?/V10aAZXn0KNmAV/BG2m5rrZuPTORl75ywC2osjcCc0CCZ32MprhM14VGZY!/b/YfH4NzzxFAAAYiAwRDwLFQAA][/img]
现在假如word的上层目录变为test1234，则包括word和其下层的所有目录也不显示出来即word和其下面的word_test以及word_test_test都不要显示出来。
如图：
[img=http://b100.photo.store.qq.com/psb?/V10aAZXn0KNmAV/brfMRMfQah.rXZeR4uWtcGc*o.0wJZlp7HvrvKGVJMM!/b/YSzgnTtMFQAAYsEJpzsxFgAA][/img]
我用的是嵌套子查询，只做了三层目录的嵌套，如果还有四层，五层目录则再嵌套之前的语句，非常笨而且比较死的方法，其中role001是一个带进来的参数，可以忽略不管。
代码如下：
select * from
(
select * from
(
select * from tlpdmd where tlpdmd01='role001'
and tdma03
not in(select tdma03 from tlpdmd where tdma03 not in(select tdma01 from tlpdmd) and tdma03<>'root')
) x
where x.tdma03
in
(
select tdma01 from tlpdmd where tlpdmd01='role001'
and tdma03
not in(select tdma03 from tlpdmd where tdma03 not in(select tdma01 from tlpdmd) and tdma03<>'root')
) or x.tdma03='root'
)y
where y.tdma03
in
(
select tdma01 from
(
select * from tlpdmd where tlpdmd01='role001'
and tdma03
not in(select tdma03 from tlpdmd where tdma03 not in(select tdma01 from tlpdmd) and tdma03<>'root')
) x
where x.tdma03
in
(
select tdma01 from tlpdmd where tlpdmd01='role001'
and tdma03
not in(select tdma03 from tlpdmd where tdma03 not in(select tdma01 from tlpdmd) and tdma03<>'root')
) or x.tdma03='root'
) or y.tdma03='root'
USE tempdb
GO
-- 建立演示环境
CREATE TABLE Dept(
id int PRIMARY KEY,
parent_id int,
name nvarchar(20))
INSERT Dept
SELECT 0, 0, N'<全部>' UNION ALL
SELECT 1, 0, N'财务部' UNION ALL
SELECT 2, 0, N'行政部' UNION ALL
SELECT 3, 0, N'业务部' UNION ALL
SELECT 4, 0, N'业务部' UNION ALL
SELECT 5, 4, N'销售部' UNION ALL
SELECT 6, 4, N'MIS' UNION ALL
SELECT 7, 6, N'UI' UNION ALL
SELECT 8, 6, N'软件开发' UNION ALL
SELECT 9, 8, N'内部开发'
GO
-- 查询指定部门下面的所有部门
DECLARE @Dept_name nvarchar(20)
SET @Dept_name = N'MIS'
;WITH
DEPTS AS(
-- 定位点成员
SELECT * FROM Dept
WHERE name = @Dept_name
UNION ALL
-- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归
SELECT A.*
FROM Dept A, DEPTS B
WHERE A.parent_id = B.id
)
SELECT * FROM DEPTS
GO
-- 删除演示环境
DROP TABLE Dept
----CTE的综合应用
USE tempdb
GO
-- 建立演示环境
CREATE TABLE Dept(
id int PRIMARY KEY,
parent_id int,
name nvarchar(20))
INSERT Dept
SELECT 0, 0, N'<全部>' UNION ALL
SELECT 1, 0, N'财务部' UNION ALL
SELECT 2, 0, N'行政部' UNION ALL
SELECT 3, 0, N'业务部' UNION ALL
SELECT 4, 0, N'业务部' UNION ALL
SELECT 5, 4, N'销售部' UNION ALL
SELECT 6, 4, N'MIS' UNION ALL
SELECT 7, 6, N'UI' UNION ALL
SELECT 8, 6, N'软件开发' UNION ALL
SELECT 9, 8, N'内部开发'
GO
-- 查询指定部门下面的所有部门, 并汇总各部门的下级部门数
DECLARE @Dept_name nvarchar(20)
SET @Dept_name = N'MIS'
;WITH
DEPTS AS( -- 查询指定部门及其下的所有子部门
-- 定位点成员
SELECT * FROM Dept
WHERE name = @Dept_name
UNION ALL
-- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归
SELECT A.*
FROM Dept A, DEPTS B
WHERE A.parent_id = B.id
),
DEPTCHILD AS( -- 引用第1个CTE,查询其每条记录对应的部门下的所有子部门
SELECT
Dept_id = P.id, C.id, C.parent_id
FROM DEPTS P, Dept C
WHERE P.id = C.parent_id
UNION ALL
SELECT
P.Dept_id, C.id, C.parent_id
FROM DEPTCHILD P, Dept C
WHERE P.id = C.parent_id
),
DEPTCHILDCNT AS( -- 引用第2个CTE, 汇总得到各部门下的子部门数
SELECT
Dept_id, Cnt = COUNT(*)
FROM DEPTCHILD
GROUP BY Dept_id
)
SELECT -- JOIN第1,3个CTE,得到最终的查询结果
D.*,
ChildDeptCount = ISNULL(DS.Cnt, 0)
FROM DEPTS D
LEFT JOIN DEPTCHILDCNT DS
ON D.id = DS.Dept_id
GO
-- 删除演示环境
DROP TABLE Dept
CREATE PROC p_Move_CopyCode
@TableName sysname, --调整编码规则的表名
@FieldName sysname, --编码字段名
@CodeRule varchar(50), --以逗号分隔的编码规则，每层编码的长度，比如1,2,3,表示有三层编码，第一层长度为1，第二层长度为2，第三层长度为3
@Code varchar(50), --要复制或者移动的节点编码
@ParentCode varchar(50), --移动到该编码的节点下
@IsCopy bit=0 --0为移动处理,否则为复制处理
AS
--参数检查
IF ISNULL(OBJECTPROPERTY(OBJECT_ID(@TableName),N'IsUserTable'),0)=0
BEGIN
RAISERROR(N'"%s"不存在,或者不是用户表',1,16,@TableName)
RETURN
END
IF NOT EXISTS(SELECT * FROM SYSCOLUMNS WHERE ID=OBJECT_ID(@TableName) AND name=@FieldName)
BEGIN
RAISERROR(N'列名"%s"在用户表"%s"中无效',1,16,@FieldName,@TableName)
RETURN
END
IF ISNULL(@CodeRule,'')=''
BEGIN
RAISERROR(N'必须编码规则字符串',1,16)
RETURN
END
IF PATINDEX(N'%[^0-9^,]%',@CodeRule)>0
BEGIN
RAISERROR(N'编码规则字符串"%s"中只能包含数字和逗号(,)',1,16,@CodeRule)
RETURN
END
IF ISNULL(@Code,'')='' RETURN
--生成编码规则修改字符串
DECLARE @CodeLen int,@CodeLens varchar(10),@Pos varchar(10),
@Old_CodeRule varchar(50),@New_CodeRule varchar(50),
@s nvarchar(4000),
@Code1 varchar(100),@Code2 varchar(100)
IF ISNULL(@ParentCode,'')=''
SELECT @ParentCode=N'',
@New_CodeRule=@CodeRule
SET @CodeLens=0
WHILE CHARINDEX(N',',@CodeRule)>0
BEGIN
SET @CodeLen=LEFT(@CodeRule,CHARINDEX(N',',@CodeRule)-1)
IF @CodeLens+@CodeLen=LEN(@Code)
BEGIN
SELECT @Old_CodeRule=@CodeRule,
@Pos=@CodeLens
IF @New_CodeRule>'' GOTO lb_CalcCodeLens
END
SELECT @CodeRule=STUFF(@CodeRule,1,CHARINDEX(N',',@CodeRule),N''),
@CodeLens=@CodeLens+@CodeLen
IF @CodeLens=LEN(@ParentCode)
BEGIN
SET @New_CodeRule=@CodeRule
IF @Old_CodeRule>'' GOTO lb_CalcCodeLens
END
END
IF @Old_CodeRule IS NULL AND CAST(@CodeLens as int)+@CodeRule=LEN(@Code)
SELECT @Old_CodeRule=@CodeRule,
@Pos=@CodeLens
IF @New_CodeRule IS NULL AND CAST(@CodeLens as int)+@CodeRule=LEN(@ParentCode)
BEGIN
RAISERROR(N'移动编码"%s"到编码"%s"下导致编码长度溢出编码规则允许的长度',1,16,@Code,@ParentCode)
RETURN
END
lb_CalcCodeLens:
SET @s=N'SET @CodeLens=@CodeLens+'+REPLACE(@CodeRule,N',',N'+')
EXEC sp_executesql @s,N'@CodeLens int OUTPUT',@CodeLens OUTPUT
IF @Old_CodeRule IS NULL
BEGIN
RAISERROR(N'编码"%s"不符合指定的编码规则',1,16,@Code)
RETURN
END
IF @New_CodeRule IS NULL
BEGIN
RAISERROR(N'编码"%s"不符合指定的编码规则',1,16,@ParentCode)
RETURN
END
DECLARE @Parent_Chk nvarchar(4000),@Delete_old nvarchar(4000),@where nvarchar(4000)
SELECT @TableName=QUOTENAME(@TableName),
@FieldName=QUOTENAME(@FieldName),
@s=CASE
WHEN @Old_CodeRule=@New_CodeRule THEN N'New_no'
ELSE dbo.f_ChangeCodeRule(@Old_CodeRule,@New_CodeRule,'',0,N'New_No')
END,
@Code1=QUOTENAME(@Code+N'%',N''''),
@Code2=QUOTENAME(@Code,N''''),
@ParentCode=QUOTENAME(@ParentCode,N''''),
@Parent_Chk=CASE
WHEN @ParentCode=N'''''' THEN N''
ELSE 'IF NOT EXISTS(SELECT * FROM '+@TableName
+N' WHERE '+@FieldName+N'='+@ParentCode+N')
BEGIN
RAISERROR(N''编码"%s"不存在'',1,16,'+@ParentCode+N')
ROLLBACK TRAN
RETURN
END' END,
@Delete_old=CASE
WHEN @IsCopy=0
THEN N'DELETE a FROM '+@TableName
+N' a,# b WHERE a.'+@FieldName+N'=b.Old_No'
ELSE N'' END,
@where=CASE
WHEN @IsCopy=0 THEN N'AND Old_No<>a.Old_No'
ELSE N'' END
--检查并完成删除处理
EXEC(N'BEGIN TRAN
'+@Parent_Chk+N'
--将处理后的编码与处理前的编码保存到临时表
SELECT Old_No,New_No='+@ParentCode+N'+'+@s+N'
INTO # FROM(
SELECT Old_No='+@FieldName+N',
New_No=STUFF('+@FieldName+N',1,'+@Pos+N','''')
FROM '+@TableName+N' WITH(XLOCK,TABLOCK)
WHERE '+@FieldName+N' LIKE '+@Code1+N')a
--编码重复检测
SELECT Err=N''超过编码规则能处理的长度'',* INTO #1 FROM # WHERE LEN(New_No)>'+@CodeLens+N'
UNION ALL
SELECT Err=N''转换后编码重复'',* FROM # a
WHERE EXISTS(
SELECT * FROM # WHERE New_No=a.New_No AND Old_No<>a.Old_No)
UNION ALL
SELECT Err=N''转换后与表中现有的编码重复'',* FROM # a
WHERE EXISTS(
SELECT * FROM '+@TableName+N'
WHERE '+@FieldName+N'=a.New_No '+@where+N')
IF @@ROWCOUNT>0
SELECT * FROM #1 ORDER BY Err,New_No
ELSE
BEGIN
--移动编码
SELECT a.* INTO #2
FROM '+@TableName+N' a,# b
WHERE a.'+@FieldName+N'=b.Old_No
UPDATE a SET '+@FieldName+N'=b.New_No
FROM #2 a,# b
WHERE a.'+@FieldName+N'=b.Old_No
'+@Delete_old+N' --如果是移动,先进行删除处理
INSERT '+@TableName+N' SELECT * FROM #2
END
COMMIT TRAN')
