网上方法很大,也许此方案只是其中一种,使用ID ParentID FullPath 三字段来描述树结构,对实际树型结构数据有如下假设:
1、数据总量不大,如果数据量很大,会考虑用多张表来描述其数据关系,不会考虑在一张表来实现树型结构
2、结点深度可以接受,这样FullPath产生的冗余数据也可以接受,查询效率也会较高
3、经常需要根据结构关系查询
Create table Dept (DeptID int, ParentID int , DeptName nvarchar(50), FullPath varchar(256));
FullPath 以 /Parent1/Parent2/DeptId形式存储
取@DeptId(@FullPath)下所有子部门
SELECT * FROM Dept WHERE FullPath LIKE @FullPath+"%'
将@DeptID1(@FullPath1) 下的所有子部门移到 @DeptID2(@FullPath2)下
UPDATE Dept SET ParentID = @DeptID2 WHERE ParentID = @DeptID1;
UPDATE Dept SET FullPath = REPLACE(FullPath,@FullPath1,@FullPath2) WHERE FullPath LIKE @FullPath1+'%' AND DeptID != @DeptID1;
删除@DeptID(@FullPath),保留其下子部门
UPDATE Dept SET ParentID = NULL WHERE ParentID = @DeptID;
UPDATE Dept SET FullPath = REPLACE(FullPath,@FullPath,'') WHERE FullPath LIKE @FullPath+'%' ;
DELETE FROM Dept WHERE DeptID = @DeptID;