SQL应用之依赖计算与回路检测

依赖关系是数据库表中比较常见的一种设计，一个典型的应用就是薪资模块。具备这种依赖关系的数据结构需要解决“依赖计算”“循环检测”“依赖查看”等问题。本文旨在提供解决这些问题的一种通用解决方案。

什么是依赖计算？

举一个典型的例子，设计薪资模块，一个工资项，即可以是固定值value，也可以是由其它的一个或多个工资项通过表达式得到的结果。

工资项	依赖工资项
A	value1
B	value2
C	A - B + value3
D	C * 0.8
…	…

很明显工资项A、B不依赖任何项，工资项C依赖于A和B，而D依赖于C。在计算最终工资时，必须按照这种依赖顺序，才能够得到最终的准确结果。把工资按这种依赖顺序计算出来，就是“依赖计算”。

什么是回路检测呢？

细心的人，也许很快就反应过来，如果这里的依赖关系不严格（如A依赖B，B又依赖A），出现“依赖死循环”，该怎么办呢？这就是下面要解决的问题：“回路检测”，也许称为“循环检测”更恰当。在计算依赖之前必须通过“回路检测”保证数据有效性。

回路检测，除了找出具有循环依赖的数据项之外，还要找出这些循环依赖的过程。

-- @RTable存储依赖关系的数据
declare @RTable table(Id int,Name varchar(50),RefId int)
-- @table数据项列表，OrderNo表示其计算顺序编号
declare @table table(Id int,Name varchar(50),OrderNo int)

insert into @RTable values(1,'A',2),(2,'B',3),(3,'C',4),(4,'D',1),
    (5,'E',6),(6,'F',7),(7,'G',5),
    (8,'H',9),(9,'I',8),
    (10,'J',10),
    (11,'K',12),(12,'L',13),(12,'L',14),(13,'M',15),(14,'N',16),(15,'O',16)

insert into @table(Id,Name) select distinct Id,Name from @RTable
insert into @table(Id,Name) values(28,'AB'),(28,'AC'),(16,'P')

-- ===================== 回路检测(正向) =======================
-- 1、检测回路(IsCircle就是用于判断是否循环)
declare @RouteTable table(RouteNo int,RouteOrder int,Id int,IsCircle bit)
declare @resultTable table(RowNum int identity(1,1),Id int,Name varchar(50),InCircle bit,Marked bit/*查找回路路线使用*/)
insert into @resultTable(Id,Name) select Id,Name from @table

declare @i int,@maxi int,@tmpId int,@tmpRefId int--,@flag bit = 0
set @i = 1
set @maxi = (select COUNT(*) from @resultTable)
while @i <= @maxi
  begin
    select @tmpId = Id from @resultTable where RowNum = @i

    set @tmpRefId = @tmpId
    --set @flag = 0
    while 1=1
      begin
        -- 判断引用到末结点时跳出循环
        if not exists(select 1 from @RTable where Id = @tmpRefId)
          begin
            break
          end
        -- 下一步
        select @tmpRefId = RefId from @RTable where Id = @tmpRefId 
        -- 判断循环引用时跳出循环
        if @tmpRefId = @tmpId
          begin
            --set @flag = 1
            update @resultTable set InCircle = 1 where RowNum = @i -- 更新状态
            break
          end
      end

    set @i += 1
  end

select * from @resultTable where InCircle = 1

-- 2、计算回路
declare @tmpNode table(Id int)
declare @RouteCount int = 1,@IsMarked bit
set @i = 1
set @maxi = (select COUNT(*) from @resultTable)
while @i <= @maxi
  begin
    select @tmpId = Id,@IsMarked = Marked from @resultTable where RowNum = @i
    if @IsMarked = 1
      begin
        set @i += 1
        continue
      end

    set @tmpRefId = @tmpId
    delete @tmpNode
    while 1=1
      begin
        -- 判断引用到末结点时跳出循环(非回路)
        if not exists(select 1 from @RTable where Id = @tmpRefId)
          begin
            break
          end
        -- 下一步
        select @tmpRefId = RefId from @RTable where Id = @tmpRefId 
        insert into @tmpNode select @tmpRefId
        -- 判断循环引用时跳出循环(是回路)
        if @tmpRefId = @tmpId
          begin
            --set @flag = 1
            insert into @RouteTable(RouteNo,RouteOrder,Id,IsCircle) select @RouteCount,ROW_NUMBER() over(order by Id),Id,1 from @tmpNode
            set @RouteCount += 1
            update @resultTable set Marked = 1 where Id in(select Id from @tmpNode)

            update @resultTable set InCircle = 1 where RowNum = @i -- 更新状态
            break
          end
      end

    set @i += 1
  end

select * from @RouteTable

---- ===================== 忽略策略计算依赖顺序 =======================
---- 排除自身依赖（原因：1->1,1->2 => 1->2）
--delete @RTable where Id = RefId
--update @table set OrderNo = 0 where Id not in(select Id from @RTable)

---- 3、采用回路忽略策略,计算依赖顺序
--declare @tmpTable table(Id int,RefMax int)
--while 1=1
--  begin
--  -- 清空临时表
--  delete @tmpTable

--  insert into @tmpTable(Id,RefMax)
--  select t.Id,t2.TMax 
--  from @table t 
--  left join (
--      select r.Id,COUNT(*) as Total,MAX(t.OrderNo) as TMax,
--          SUM(CASE WHEN t.OrderNo IS NOT NULL THEN 1 ELSE 0 END) as TCount
--      from @RTable r  
--          left join @table t on t.Id = r.RefId
--      group by r.Id
--  ) t2 on t2.Id = t.Id 
--  where t2.Total = t2.TCount
--      and t.OrderNo is null       

--  if not exists(select 1 from @tmpTable)
--    begin
--      break;
--    end

--  update @table set OrderNo = t.RefMax + 1 from @tmpTable t where t.Id = [@table].Id
--  end

---- 查看计算顺序
--select * from @table

---- 查询所有回路数据(从侧面查看回路数据)
--select * from @table where OrderNo is null

-- ======================= 向上向下查找所有依赖项 ========================
---- 4、向上找某结点所有依赖项
declare @Id int = 12
;WITH cte AS
(  
    -- 起始条件
    SELECT Id,RefId FROM @RTable as t WHERE Id = @Id
    UNION ALL  
    -- 递归条件
    SELECT t.Id,t.RefId FROM @RTable as t,cte t2 WHERE t.RefId = t2.Id and t.Id <> @Id
)
select c.Id,t.Name,c.RefId from cte c left join @table t on t.Id = c.Id

---- 5、向下找某结点所有依赖项:查看某结点所在路线之后的所有数据(在回路返回当前结点时中止)
--declare @Id int = 11
set @Id = 12
;WITH cte AS
(  
    -- 起始条件
    SELECT Id,RefId FROM @RTable as t WHERE Id = @Id
    UNION ALL  
    -- 递归条件
    SELECT t.Id,t.RefId FROM @RTable as t,cte t2 WHERE t.Id = t2.RefId and t.RefId <> @Id
)
select c.Id,t.Name,c.RefId from cte c left join @table t on t.Id = c.Id