-- 查询树节点(带级别),按照前序遍历排序
SELECT tree2.ResultID ,
tree2.LeftID ,
tree2.RightID ,
tree2.ArgumentID ,
tree2.FunID ,
COUNT(*) layer
FROM T_Trace_Geological tree1
RIGHT JOIN T_Trace_Geological tree2 ON ( tree1.LeftID <= tree2.LeftID
AND tree1.RightID >= tree2.RightID
)
GROUP BY tree2.ResultID ,
tree2.LeftID ,
tree2.RightID ,
tree2.ArgumentID ,
tree2.FunID
ORDER BY tree2.LeftID
-- 增加节点(其中3为上级节点ID,在3的节点上增加一个子节点)
UPDATE T_Trace_Geological
SET LeftID = LeftID + 2
WHERE LeftID >= ( SELECT RightID
FROM T_Trace_Geological
WHERE ResultID = 3
)
UPDATE T_Trace_Geological
SET RightID = RightID + 2
WHERE RightID >= ( SELECT RightID
FROM T_Trace_Geological
WHERE ResultID = 3
)
INSERT INTO T_Trace_Geological
( ResultID ,
LeftID ,
RightID ,
ArgumentID ,
FunID
)
SELECT 10 , --新增节点ID
RightID - 2 ,
RightID - 1 ,
10 ,
10
FROM T_Trace_Geological
WHERE ResultID = 3
-- 删除节点(其中10节点ID)
DELETE tree1
FROM T_Trace_Geological tree1
INNER JOIN ( SELECT LeftID ,
RightID
FROM T_Trace_Geological
WHERE ResultID = 10
) tree2 ON tree1.LeftID > tree2.LeftID
AND tree1.RightID < tree2.RightID
UPDATE tree1
SET tree1.LeftID = tree1.LeftID - ( tree2.RightID - tree2.LeftID + 1 )
FROM T_Trace_Geological tree1
INNER JOIN ( SELECT LeftID ,
RightID
FROM T_Trace_Geological
WHERE ResultID = 10
) tree2 ON tree1.LeftID > tree2.LeftID
UPDATE tree1
SET tree1.RightID = tree1.RightID - ( tree2.RightID - tree2.LeftID + 1 )
FROM T_Trace_Geological tree1
INNER JOIN ( SELECT LeftID ,
RightID
FROM T_Trace_Geological
WHERE ResultID = 10
) tree2 ON tree1.RightID > tree2.RightID
DELETE FROM T_Trace_Geological
WHERE ResultID = 10
--获得子树(2为子树的根)
SELECT tree1.*
FROM T_Trace_Geological tree1
INNER JOIN ( SELECT LeftID ,
RightID
FROM T_Trace_Geological
WHERE ResultID = 2
) tree2 ON tree1.LeftID >= tree2.LeftID
AND tree1.RightID <= tree2.RightID
--获得父节点路径(3当前节点)
SELECT tree1.*
FROM T_Trace_Geological tree1
INNER JOIN ( SELECT LeftID ,
RightID
FROM T_Trace_Geological
WHERE ResultID = 3
) tree2 ON tree1.LeftID < tree2.LeftID
AND tree1.RightID > tree2.RightID
---- sqlMap 执行多条sql示例
--<statement id="DeleteAccount" parameterClass="Account">
-- BEGIN DELETE FROM ORDERS WHERE ACCOUNT_ID = #Id#;
-- DELETE FROM ACCOUNTS WHERE ACCOUNT_ID = #Id#; END;
-- </statement>
SELECT tree2.ResultID ,
tree2.LeftID ,
tree2.RightID ,
tree2.ArgumentID ,
tree2.FunID ,
COUNT(*) layer
FROM T_Trace_Geological tree1
RIGHT JOIN T_Trace_Geological tree2 ON ( tree1.LeftID <= tree2.LeftID
AND tree1.RightID >= tree2.RightID
)
GROUP BY tree2.ResultID ,
tree2.LeftID ,
tree2.RightID ,
tree2.ArgumentID ,
tree2.FunID
ORDER BY tree2.LeftID
-- 增加节点(其中3为上级节点ID,在3的节点上增加一个子节点)
UPDATE T_Trace_Geological
SET LeftID = LeftID + 2
WHERE LeftID >= ( SELECT RightID
FROM T_Trace_Geological
WHERE ResultID = 3
)
UPDATE T_Trace_Geological
SET RightID = RightID + 2
WHERE RightID >= ( SELECT RightID
FROM T_Trace_Geological
WHERE ResultID = 3
)
INSERT INTO T_Trace_Geological
( ResultID ,
LeftID ,
RightID ,
ArgumentID ,
FunID
)
SELECT 10 , --新增节点ID
RightID - 2 ,
RightID - 1 ,
10 ,
10
FROM T_Trace_Geological
WHERE ResultID = 3
-- 删除节点(其中10节点ID)
DELETE tree1
FROM T_Trace_Geological tree1
INNER JOIN ( SELECT LeftID ,
RightID
FROM T_Trace_Geological
WHERE ResultID = 10
) tree2 ON tree1.LeftID > tree2.LeftID
AND tree1.RightID < tree2.RightID
UPDATE tree1
SET tree1.LeftID = tree1.LeftID - ( tree2.RightID - tree2.LeftID + 1 )
FROM T_Trace_Geological tree1
INNER JOIN ( SELECT LeftID ,
RightID
FROM T_Trace_Geological
WHERE ResultID = 10
) tree2 ON tree1.LeftID > tree2.LeftID
UPDATE tree1
SET tree1.RightID = tree1.RightID - ( tree2.RightID - tree2.LeftID + 1 )
FROM T_Trace_Geological tree1
INNER JOIN ( SELECT LeftID ,
RightID
FROM T_Trace_Geological
WHERE ResultID = 10
) tree2 ON tree1.RightID > tree2.RightID
DELETE FROM T_Trace_Geological
WHERE ResultID = 10
--获得子树(2为子树的根)
SELECT tree1.*
FROM T_Trace_Geological tree1
INNER JOIN ( SELECT LeftID ,
RightID
FROM T_Trace_Geological
WHERE ResultID = 2
) tree2 ON tree1.LeftID >= tree2.LeftID
AND tree1.RightID <= tree2.RightID
--获得父节点路径(3当前节点)
SELECT tree1.*
FROM T_Trace_Geological tree1
INNER JOIN ( SELECT LeftID ,
RightID
FROM T_Trace_Geological
WHERE ResultID = 3
) tree2 ON tree1.LeftID < tree2.LeftID
AND tree1.RightID > tree2.RightID
---- sqlMap 执行多条sql示例
--<statement id="DeleteAccount" parameterClass="Account">
-- BEGIN DELETE FROM ORDERS WHERE ACCOUNT_ID = #Id#;
-- DELETE FROM ACCOUNTS WHERE ACCOUNT_ID = #Id#; END;
-- </statement>