代码之家 › 专栏 › 技术社区 › Daan

递归查询中的条件

hierarchy recursion sql-server sql

Daan · 技术社区 · 16 年前

我在MS SQL中有以下递归表值函数,以便从数据库中检索对象的层次结构:


WITH tmpField (ParentNum, ChildNum, FieldNum, FieldDescr, Iteration) AS
(
  SELECT Field.ParentNum, Field.ChildNum, Field.FieldNum, Field.FieldDescr, 1
  FROM Field
  WHERE Field.ParentNum = @ParentNum

  UNION ALL 

  SELECT Field.ParentNum, Field.ChildNum, Field.FieldNum, Field.FieldDescr, tmpField.Iteration + 1
  FROM Field INNER JOIN 
  tmpField on Field.ParentNum = tmpField.ChildNum
)
SELECT DISTINCT ParentNum AS ParentNum, ChildNum AS ChildNum, FieldNum, FieldDescr
FROM tmpField

我想用以下方式修改它:

在 最后的 迭代,当没有更多的“孩子”时,我想要 ChildNum 要具有值的字段 FieldNum . 在所有之前的迭代中, 分娩期 应该具有 分娩期 就像现在一样。

有人能建议一种方法来实现这一点吗,将上面的查询作为起点?

请注意:尽管它的名字,字段 分娩期 不引用行的任何子级,但应将其解释为该行的标识符。

2 回复 | 直到 16 年前

manji 16 年前

如果没有更多的子级,则表示childnum为空,因此:

...

UNION ALL 

  SELECT Field.ParentNum, 
         COALESCE(Field.ChildNum, Field.FieldNum) ChildNum,
         Field.FieldNum,
         ...

编辑:(跟随Daan评论)

好的,在这种情况下,我们可以检查childnum‘children’计数:

    ...

    UNION ALL 

      SELECT F1.ParentNum, 
             CASE WHEN (SELECT COUNT(1) 
                          FROM FIELD F2 
                         WHERE F2.ParentNum = F1.ChildNum) = 0 
                  THEN F1.FieldNum
                  ELSE F1.ChildNum
             END ChildNum,
             F1.FieldNum, F1.FieldDescr, tmpField.Iteration + 1
      FROM Field F1 INNER JOIN 
      tmpField on F1.ParentNum = tmpField.ChildNum

...

编辑2:

让我们把支票移到外面:

WITH tmpField (ParentNum, ChildNum, FieldNum, FieldDescr, Iteration) AS
(
  SELECT Field.ParentNum, Field.ChildNum, Field.FieldNum, Field.FieldDescr, 1
  FROM Field
  WHERE Field.ParentNum = @ParentNum

  UNION ALL 

  SELECT Field.ParentNum, Field.ChildNum, Field.FieldNum, Field.FieldDescr, tmpField.Iteration + 1
  FROM Field INNER JOIN 
  tmpField on Field.ParentNum = tmpField.ChildNum
)
SELECT DISTINCT ParentNum AS ParentNum, 
                CASE WHEN EXISTS (SELECT NULL 
                                    FROM Field f 
                                   WHERE tmpField.ChildNum = f.ParentNum) 
                      THEN tmpField.ChildNum
                      ELSE tmpField.FieldNum
                 END ChildNum,
                FieldNum,
                FieldDescr
FROM tmpField

Niikola 16 年前

这将返回您需要的数据。我删除了迭代,因为您以后不使用它

加入版本

;WITH tmpField (ParentNum, ChildNum, FieldNum, FieldDescr) AS
(
  SELECT f.ParentNum, f.ChildNum, f.FieldNum, f.FieldDescr
    FROM Field f
   WHERE f.ParentNum = @ParentNum
  UNION ALL 
  SELECT f.ParentNum, f.ChildNum, f.FieldNum, f.FieldDescr
    FROM Field f 
   INNER JOIN tmpField on f.ParentNum = tmpField.ChildNum
)
SELECT t.ParentNum AS ParentNum, 
       Case When p.ParentNum is Null 
            Then t.FieldNum 
            Else t.ChildNum 
        End AS ChildNum, 
       t.FieldNum, 
       t.FieldDescr
FROM tmpField t
Left Join (Select distinct ParentNum From Field) p on t.ChildNum=p.ParentNum

或

子查询版本(修改为使用exists而不是count)

;WITH tmpField (ParentNum, ChildNum, FieldNum, FieldDescr) AS
(
  SELECT f.ParentNum, f.ChildNum, f.FieldNum, f.FieldDescr
    FROM Field f
   WHERE f.ParentNum = @ParentNum
  UNION ALL 
  SELECT f.ParentNum, f.ChildNum, f.FieldNum, f.FieldDescr
    FROM Field f 
   INNER JOIN tmpField on f.ParentNum = tmpField.ChildNum
)
SELECT t.ParentNum AS ParentNum, 
       Case When Exists(Select * from Field p Where t.ChildNum=p.ParentNum)
            Then t.ChildNum 
            Else t.FieldNum 
        End AS ChildNum, 
       t.FieldNum, 
       t.FieldDescr
FROM tmpField t