递归查询中止

Question

我有一张桌子，里面有配偶、父亲和母亲等家庭成员的详细情况。现在，基于此，我需要获取这样的数据：

Spouse

children

children spouse and then their children

英语不是我的母语。请原谅我的任何错误，我希望我是清楚的。否则请告诉我。

为此，我创建了这个递归查询(我第一次使用这个查询)。对于某些节点，它显示的是数据，而对于其他节点，它显示的是在1001次迭代后中止的递归查询等错误。我尝试将@@cte_max_recursion_depth增加到更大的值。

这是我的查询

WITH RECURSIVE family_tree1_hierarchy AS
( SELECT rsc.id,rsc.Name,rsc.spouse,0 AS lvl FROM main_table AS rsc
WHERE rsc.id='0035000002hLlbUAAS'
UNION
SELECT rsc1.id,rsc1.Name,rsc1.spouse,lvl+1 AS lvl FROM family_tree1_hierarchy AS fth
INNER JOIN main_table AS rsc1 ON fth.id=rsc1.spouse
UNION
SELECT rsc2.id,rsc2.Name,rsc2.spouse,lvl+1 AS lvl FROM family_tree1_hierarchy AS fth2
INNER JOIN main_table AS rsc2 ON fth2.id=rsc2.Father
UNION
SELECT rsc3.id,rsc3.Name,rsc3.spouse,lvl+1 AS lvl FROM family_tree1_hierarchy AS fth3
INNER JOIN main_table AS rsc3 ON fth3.id=rsc3.Mother
)
SELECT * FROM family_tree1_hierarchy

Answer 1

Suggested fix：在查询的第二个UNION部分(在查询中找到配偶)只使用lvl而不是lvl+1。

Why上面的工作原理以及为什么它在lvl+1中失败:原始查询以(非递归部分)一个人(让我们调用她的D3)开始，并将0 AS lvl分配给她：

WITH RECURSIVE family_tree1_hierarchy AS
( SELECT rsc.id,rsc.Name, rsc.spouse, 0 AS lvl 
  FROM main_table AS rsc
  WHERE rsc.i d= '0035000002hLlbUAAS'
  ...

然后，它继续(三个递归部分)寻找配偶和子女。

当第一个人(或后来找到的某个人)有配偶(我们叫他Bob)时，问题就出现了。配偶被指定为lvl + 1 (如果第一个人有配偶，则为SO1)。然后继续(在下一次迭代中)发现Bob也有配偶(是的，这是已经找到的Alex！但是根据cte，它将新发现的Alex (这是一个重复的)级别分配给Bob 1，因此这个Alex以lvl的形式获得2。

关键的一点是，这个(重复)行的名称、id和所有其他细节都与原始的Alex相同，但lvl除外，后者现在是2而不是0。因此，该行被添加到CTE结果中，我们继续进行下一次迭代。

然后，我们再次找到鲍勃(作为亚历克斯-2的配偶)，并指定他3作为lvl。而且还在继续。CTE生成行：

Alex 0
Bob  1
Alex 2
Bob  3
Alex 4
Bob  5
...

直到达到1001迭代阈值和错误为止。

当我们找到配偶(只为孩子)时，我们的解决办法是不提高水平。所以我们仍然分配给Alex 0，但是Bob也得到0。当我们在下一次迭代中再次找到Alex时(作为Bob-0的配偶)，她得到了Alex-0，因此该行不会添加到CTE结果中(因为我们使用UNION，所以没有添加重复项)和循环中断。