嵌套集合模型（Nested Set Model）父节点视角双边配对异常排查

我先梳理下当前的情况：你已经基于嵌套集合模型实现了父节点左右子节点的检索，还加上了深度限制器，但查询结果里出现了配对异常的问题，需要实现父节点视角下的正确双边配对。下面我把你的现有实现整理清楚，再给出针对性的修复方案：

一、当前实现概述 #

• 已完成功能：检索父节点的左/右子节点、添加深度限制器
• 采用数据模型：嵌套集合模型（Nested Set Model）

二、表结构（MySQL Workbench可直接导入） #

表名：nested_category

category_id,name,lft,rgt,myorder
1,ELECTRONICS,1,30,right
2,TELEVISIONS,2,15,left
3,TUBE,3,8,left
4,LCD,9,14,right
6,"PORTABLE ELECTRONICS",16,29,right
7,"MP3 PLAYERS",17,22,left
8,FLASH,20,21,right
9,"CD PLAYERS",23,28,right
10,AIR,6,7,right
12,LIQUID,4,5,left
14,LED,12,13,right
17,BLUETOOTH,18,19,left
18,"BLUE RAY",26,27,right
19,DVD,24,25,left
20,FLORECENT,10,11,left

三、初始查询语句（存在配对异常） #

这是你最初尝试的查询逻辑，仅需手动设置@VarToPairfind变量：

SET @VarToPairfind := 'ELECTRONICS';/*手动设置*/
SET @VarFullRightSideKey :=0;/*动态设置*/
SET @VarFullLeftSideKey :=0;/*动态设置*/
SELECT @VarFullLeftSideKey:=node.lft+1 as fullLeft,@VarFullRightSideKey:=node.rgt-1 as fullRight FROM nested_category AS node where node.name=@VarToPairfind;
SET @VarFullRightSideKeyName :='';/*动态设置*/
SET @VarFullLeftSideNameKey :='';/*动态设置*/
SELECT @VarFullRightSideKeyName:=node.name From nested_category as node where node.rgt=@VarFullRightSideKey;
SELECT @VarFullLeftSideNameKey:=node.name From nested_category as node where node.lft=@VarFullLeftSideKey;
SET @rowno = 0;
SET @rownoleft = 0;
SET @rownoright = 0;
/*带深度限制的完整查询开始*/
select * from (select @rowno:=@rowno+1 as rownos,LLL.name as LLL_name,LLL.myorder as LLL_myorder,LLL.depth as LLL_depth,LLL.lft as LLL_lft,LLL.rgt as LLL_rgt,ULR.name as ULR_name,ULR.myorder as ULR_myorder,ULR.depth as ULR_depth,ULR.lft as ULR_lft,ULR.rgt as ULR_rgt from (SELECT node.name,node.myorder,node.lft,node.rgt, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth FROM nested_category AS node, nested_category AS parent, nested_category AS sub_parent, ( SELECT node.name,node.myorder, (COUNT(parent.name) - 1) AS depth,node.lft,node.rgt FROM nested_category AS node, nested_category AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.name = @VarFullRightSideKeyName GROUP BY node.name ORDER BY node.lft )AS sub_tree WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt AND sub_parent.name = sub_tree.name GROUP BY node.name HAVING depth > 1 and depth <= 2 ORDER BY node.lft) as ULR, (SELECT node.name,node.myorder,node.lft,node.rgt,(COUNT(parent.name) - (sub_tree.depth + 1)) AS depth FROM nested_category AS node, nested_category AS parent, nested_category AS sub_parent, ( SELECT node.name,node.myorder, (COUNT(parent.name) - 1) AS depth,node.lft,node.rgt FROM nested_category AS node, nested_category AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.name = @VarFullLeftSideNameKey GROUP BY node.name ORDER BY node.lft )AS sub_tree WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt AND sub_parent.name = sub_tree.name GROUP BY node.name HAVING depth > 1 and depth <= 2 ORDER BY node.lft) as LLL where ULR.myorder!=LLL.myorder and ULR.depth=LLL.depth group by LLL.name,ULR.name) as sidetree;

四、更新版查询语句（尝试修复但仍有异常） #

你后续尝试用临时表优化，但配对逻辑还是依赖行号奇偶性，导致异常：

SET @VarToPairfind := 'ELECTRONICS';/*手动设置*/
SET @VarFullRightSideKey :=0;/*动态设置*/
SET @VarFullLeftSideKey :=0;/*动态设置*/
SELECT @VarFullLeftSideKey:=node.lft+1 as fullLeft,@VarFullRightSideKey:=node.rgt-1 as fullRight FROM nested_category AS node where node.name=@VarToPairfind;
SET @VarFullRightSideKeyName :='';/*动态设置*/
SET @VarFullLeftSideNameKey :='';/*动态设置*/
SELECT @VarFullRightSideKeyName:=node.name From nested_category as node where node.rgt=@VarFullRightSideKey;
SELECT @VarFullLeftSideNameKey:=node.name From nested_category as node where node.lft=@VarFullLeftSideKey;
SET @rowno = 0;
SET @rownoleft = 0;
SET @rownoright = 0;
/*带深度限制的完整查询开始*/
CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (select * from (select @rowno:=@rowno+1 as rownos,LLL.name as LLL_name,LLL.myorder as LLL_myorder,LLL.depth as LLL_depth,LLL.lft as LLL_lft,LLL.rgt as LLL_rgt,ULR.name as ULR_name,ULR.myorder as ULR_myorder,ULR.depth as ULR_depth,ULR.lft as ULR_lft,ULR.rgt as ULR_rgt from (SELECT node.name,node.myorder,node.lft,node.rgt, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth FROM nested_category AS node, nested_category AS parent, nested_category AS sub_parent, ( SELECT node.name,node.myorder, (COUNT(parent.name) - 1) AS depth,node.lft,node.rgt FROM nested_category AS node, nested_category AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.name = @VarFullRightSideKeyName GROUP BY node.name ORDER BY node.lft )AS sub_tree WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt AND sub_parent.name = sub_tree.name GROUP BY node.name HAVING depth > 1 and depth <= 2 ORDER BY node.lft) as ULR, (SELECT node.name,node.myorder,node.lft,node.rgt,(COUNT(parent.name) - (sub_tree.depth + 1)) AS depth FROM nested_category AS node, nested_category AS parent, nested_category AS sub_parent, ( SELECT node.name,node.myorder, (COUNT(parent.name) - 1) AS depth,node.lft,node.rgt FROM nested_category AS node, nested_category AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.name = @VarFullLeftSideNameKey GROUP BY node.name ORDER BY node.lft )AS sub_tree WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt AND sub_parent.name = sub_tree.name GROUP BY node.name HAVING depth > 1 and depth <= 2 ORDER BY node.lft) as LLL where ULR.myorder!=LLL.myorder and ULR.depth=LLL.depth group by LLL.name,ULR.name) as sidedtree);
CREATE TEMPORARY TABLE IF NOT EXISTS PairTable LIKE table2;
INSERT INTO PairTable select * from table2 where rownos%2=0 order by rownos desc limit 2;
INSERT INTO PairTable select * from table2 where rownos%2=1 order by rownos asc limit 2;
select * from PairTable;
/*删除临时表*/
DROP TABLE PairTable;
drop TABLE table2;

五、问题核心与修复方案 #

问题根源 #

当前逻辑依赖行号奇偶性、临时表插入顺序来配对，没有基于嵌套集合的层级结构+左右子树的对称顺序来匹配，完全是随机依赖行号，必然出现错误配对。

修复思路 #

我们需要明确：父节点视角下的双边配对，应该是左子树中符合深度要求的节点，和右子树中同位置的节点一一对应。核心步骤是：

1. 先定位目标父节点的左右子树根节点
2. 分别提取左右子树中符合深度要求的节点，按lft（嵌套集合的顺序）排序并编号
3. 通过编号关联左右节点，确保配对的正确性

修正后的查询语句 #

SET @VarToPairfind := 'ELECTRONICS';

-- 1. 获取目标父节点的左右边界
SELECT @parent_lft := lft, @parent_rgt := rgt 
FROM nested_category 
WHERE name = @VarToPairfind;

-- 2. 获取左子树的顶级节点（父节点的直接左子节点）
SELECT @left_subtree_root := name 
FROM nested_category 
WHERE lft = @parent_lft + 1;

-- 3. 获取右子树的顶级节点（父节点的直接右子节点）
SELECT @right_subtree_root := name 
FROM nested_category 
WHERE rgt = @parent_rgt - 1;

-- 4. 生成左子树中深度为2的节点列表并编号（对应原需求depth>1且<=2）
CREATE TEMPORARY TABLE IF NOT EXISTS left_nodes AS
SELECT 
    @left_row := @left_row + 1 AS pair_id,
    node.name AS left_name,
    node.myorder AS left_myorder,
    node.lft AS left_lft,
    node.rgt AS left_rgt
FROM nested_category AS node
JOIN nested_category AS parent ON node.lft BETWEEN parent.lft AND parent.rgt
JOIN (SELECT @left_row := 0) AS init
WHERE parent.name = @left_subtree_root
GROUP BY node.name
HAVING COUNT(parent.name) - 1 = 2 -- 相对于子树根节点的深度为2
ORDER BY node.lft;

-- 5. 生成右子树中深度为2的节点列表并编号
CREATE TEMPORARY TABLE IF NOT EXISTS right_nodes AS
SELECT 
    @right_row := @right_row + 1 AS pair_id,
    node.name AS right_name,
    node.myorder AS right_myorder,
    node.lft AS right_lft,
    node.rgt AS right_rgt
FROM nested_category AS node
JOIN nested_category AS parent ON node.lft BETWEEN parent.lft AND parent.rgt
JOIN (SELECT @right_row := 0) AS init
WHERE parent.name = @right_subtree_root
GROUP BY node.name
HAVING COUNT(parent.name) - 1 = 2
ORDER BY node.lft;

-- 6. 按编号配对左右节点，得到正确结果
SELECT 
    left_nodes.pair_id,
    left_nodes.left_name,
    right_nodes.right_name,
    left_nodes.left_myorder,
    right_nodes.right_myorder,
    left_nodes.left_lft, left_nodes.left_rgt,
    right_nodes.right_lft, right_nodes.right_rgt
FROM left_nodes
JOIN right_nodes ON left_nodes.pair_id = right_nodes.pair_id;

-- 7. 清理临时表
DROP TABLE left_nodes;
DROP TABLE right_nodes;

说明 #

• 这个逻辑直接基于嵌套集合的层级关系计算深度，避免了复杂的子查询嵌套
• 通过给左右子树的节点分别按顺序编号，再按序号JOIN，确保每一对都是同位置的左右节点
• 可以调整HAVING COUNT(parent.name) - 1 = 2中的数字来改变深度限制（这里的2是相对于子树根节点的深度，对应原需求的深度范围）

内容的提问来源于stack exchange，提问作者DDD