mysql - 嵌套集合模型的两侧配对？从家长的角度

Question

到目前为止，我能够检索 3 个东西，即 parent 的左侧 child 、右侧 child ，我还添加了深度限制器。为了最好地了解我用过的型号，请看这个 Nested Set Model如果您选择所有内容 this is what the table looks like my slight modified version from the Nested Set model from the last link

这是我尝试的查询，这里的问题是它配对了两个正确的东西和一些奇怪的配对 the check is the correct the cross is the wrong one这就是树的样子 to visualize the tree pairing

SET @VarToPairfind := 'ELECTRONICS';/*manually set*/
SET @VarFullRightSideKey :=0;/*dynamically set*/
SET @VarFullLeftSideKey :=0;/*dynamically set*/
SELECT @VarFullLeftSideKey:=node.lft+1 as fullLeft,@VarFullRightSideKey:=node.rgt-1 as fullRight FROM nested_category AS node where node.name=@VarToPairfind;
SET @VarFullRightSideKeyName :='';/*dynamically set*/
SET @VarFullLeftSideNameKey :='';/*dynamically set*/
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;
/*start of full side with depth limit*/
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;

VarToPairfind 是仅给定变量的变量，其余的都被计算

这是 csv 导入文件到 workbench mysql表名是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

<小时/>

更新版本

SET @VarToPairfind := 'ELECTRONICS';/*manually set*/
SET @VarFullRightSideKey :=0;/*dynamically set*/
SET @VarFullLeftSideKey :=0;/*dynamically set*/
SELECT @VarFullLeftSideKey:=node.lft+1 as fullLeft,@VarFullRightSideKey:=node.rgt-1 as fullRight FROM nested_category AS node where node.name=@VarToPairfind;
SET @VarFullRightSideKeyName :='';/*dynamically set*/
SET @VarFullLeftSideNameKey :='';/*dynamically set*/
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;
/*start of full side with depth limit*/
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;

/*DROPS TEMPORARY TABLE*/

DROP TABLE PairTable;
drop TABLE table2;

Answer 1

不完美的解决方案，但它确实比较匹配两个

SET @VarToPairfind := 'ELECTRONICS';/*manually set*/
SET @VarFullRightSideKey :=0;/*dynamically set*/
SET @VarFullLeftSideKey :=0;/*dynamically set*/
SELECT @VarFullLeftSideKey:=node.lft+1 as fullLeft,@VarFullRightSideKey:=node.rgt-1 as fullRight FROM nested_category AS node where node.name=@VarToPairfind;
SET @VarFullRightSideKeyName :='';/*dynamically set*/
SET @VarFullLeftSideNameKey :='';/*dynamically set*/
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;
/*start of full side with depth limit*/
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;

/*DROPS TEMPORARY TABLE*/

DROP TABLE PairTable;
drop TABLE table2;