数据库树状结构的关系表的删除方案

浏览数：88 / 时间：2015年06月12日

所谓的树状结构的关系，即是记录之间有id，parentId关系的数据。场景是这样的：业务中用到了一张分享表，表里的主要字段有分享人，和被分享人，以及分享任务ID，可以通过一系列的分享构造成树状的结构，需求是删除任意的节点，需要删除节点下面的所有分享关联的数据。

方案1：可以把每个节点用一个标识将分享关系串起来拼接成一个字符串，表里增加一个字段记录这个路径字符串，删除节点时条件是只有包含节点关系记录的路径就删除，这种方案的灵感来自于windows创建文件夹的模式，每个文件夹都对应一个唯一的路径，删除某个文件夹下面相关的文件都都会被删除。

方案2：通过触发器，监听分享表的删除操作，删除数据时，将已删除的数据的被分享人作为分享人查询相关的记录，并干掉，以此类推，递归删除。但是这样的操作只能递归操作小于32层，否则将报错，而且这种递归调用性能也不好。不过可以借此学习下触发器的使用

ALTER TRIGGER [dbo].[myFirstTrigger] 
   ON  [dbo].[ShareTable]
FOR  DELETE
AS 
BEGIN

    --SET NOCOUNT ON;
   DECLARE @id INT,--分享ID;
           @customer VARCHAR(200),
           @tocustomer VARCHAR(200),
           @taskId VARCHAR(100)
    --SELECT @id=id,@customer=customer,@tocustomer=tocustomer FROM DELETED;
      DELETE FROM  dbo.ShareTable WHERE customer IN (SELECT tocustomer FROM DELETED)
END

方案3：通过cte递归，查询相关联的数据，他没有递归层级的限制，使用简单方便，此方案为最终确定方案。

cte的用法主要有2个方面，1是用作一个临时结果集，2是专门用来类似的递归

具体递归实现代码如下：

 WITH    SimpleRecursive
          AS (
               --基本语句  指定一条记录
              SELECT    id,
                        customer ,
                        tocustomer
               FROM     dbo.ShareTable
               WHERE    customer = ‘分享人‘
                        AND tocustomer = ‘被分享人‘
                        AND id=1     
               UNION ALL

              --递归语句
             
              SELECT     p.id,
                        p.customer ,
                        p.tocustomer
               FROM     dbo.ShareTable P
                        INNER JOIN SimpleRecursive A ON A.tocustomer = P.customer
                                    
             )
             SELECT id FROM SimpleRecursive   --获取所有匹配id