左右编码树的通用SQL语句
来源:互联网 发布:直通车关键词优化原理 编辑:程序博客网 时间:2024/05/14 23:36
-- 查询树节点(带级别),按照前序遍历排序
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>
0 0
- 左右编码树的通用SQL语句
- 通用SQL数据库的查询语句
- 通用SQL数据库的查询语句(1)
- 通用SQL数据库的查询语句(2)
- 请写出通用的Sql语句
- 通用SQL查询语句
- 查看SQLserver编码格式的SQL语句
- 查看SQLserver编码格式的SQL语句
- 更改数据库编码的sql语句
- PB9写的一个拆解SQL语句的通用函数
- mybatis3下的通用sql的select语句执行类
- PB9写的一个拆解SQL语句的通用函数
- PB9写的一个拆解SQL语句的通用函数
- PB9写的一个拆解SQL语句的通用函数
- 通用SQL数据库的查询语句 (1)
- 通用SQL数据库的查询语句 (3)
- 通用SQL数据库的查询语句 (4)
- 通用SQL数据库的查询语句 (5)
- 使用try Catch小结
- 学习:EF(Entity Framwork)结构
- STSNavigate SharePoint下载文件方法
- 学习:EF(Entity Framwork)结构
- [codility]Task description
- 左右编码树的通用SQL语句
- [codility]Task description
- 这是怎么了
- EF(Entity Framwork)
- 关于Dbeaver通用数据库连接工具 连接informix数据库中文乱码解决
- 第7周作业1——背包问题
- java序列化与反序列化以及保证数据一致性
- oracle内存表结合table()函数的使用
- 第7周作业1——背包问题