递归查询中的条件

Question

我在 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 的值。在之前的所有迭代中，ChildNum 应该具有 ChildNum 字段的值，就像现在一样。

任何人都可以建议一种以上述查询为起点来实现此目的的方法吗？

请注意：尽管有它的名称，字段 ChildNum 并不引用行的任何子项，但它应该被解释为该行的标识符。

Answer 1

当没有更多的孩子时，这意味着 ChildNum 为 NULL，所以：

...

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

...

---

EDIT2：

让我们将检查移到外面：

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

Answer 2

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

JOIN VERSION

;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

或

SUBQUERY VERSION （修改为使用 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