表结构和表数据就不公示了,查询的表user_role,主键是id,每条记录有parentid字段; 如下mysql查询函数即可实现根据一个节点查询所有的子节点,根据一个子节点查询所有的父节点.对于数据量较大的时候(我这里测试的1万条左右).查询效率非常慢.建议在java代码中进行处理. CREATE FUNCTION `getChildList`(rootId INT) ) BEGIN ); ); SET sChildTemp =cast(rootId as CHAR); WHILE sChil
--查询ID = '009'的所有父节点 ' ;WITH T AS ( SELECT ID , PID , NAME FROM TB WHERE ID = @ID UNION ALL SELECT A.ID , A.PID , A.NAME FROM TB AS A JOIN T AS B ON A.ID = B.PID ) SELECT * FROM T ORDER BY ID /* ID PID NAME ---- ---- ---------- 001 NULL 广东省 003 001 深
1.所有的子节点查询到树结构中所有父节点 RETURNS @Tree Table(PID )) as begin --DECLARE @ID VARCHAR() --SET @ID = ' ;with rolDepList as ( ) union all select a.PID,a.FID,a.Name from tbDepList a join rolDepList b on a.pid=b.fid ) Insert @Tree select distinct(PID),FID,Name
根据id查询父节点,具体需要修改的地方笔者已在注释中给大家作了注解 DELIMITER $$ USE `yjlc_platform`$$ -- getCompanyParent 为函数名 DROP FUNCTION IF EXISTS `getCompanyParent`$$ -- getCompanyParent 为函数名 rootId为参数,可以自定义:初学者可以不用更改 )) ) CHARSET utf8 BEGIN ); ); SET ptemp = '#'; SET ctemp = r
Given a binary tree, find the lowest common ancestor (LCA) of two given nodes in the tree. According to the definition of LCA on Wikipedia: “The lowest common ancestor is defined between two nodes v and w as the lowest node in T that has both v and w
Given a binary search tree (BST), find the lowest common ancestor (LCA) of two given nodes in the BST. According to the definition of LCA on Wikipedia: “The lowest common ancestor is defined between two nodes v and w as the lowest node in T that has
-- 查找所有父节点with tab as( select Type_Id,ParentId,Type_Name from Sys_ParamType_V2_0 where Type_Id=316--子节点 union all select b.Type_Id,b.ParentId,b.Type_Name from tab a,--子节点数据集 Sys_ParamType_V2_0 b --父节点数据集 where a.ParentId=b.Type_Id --子节点数据集.paren
查找所有的父节点,包括本身,不包括就<>id with tbs as(select * from TB_HomeBase where ID=223 union all select a.* from TB_HomeBase a inner join tbs b on a.id=b.ParentID) select top 1 ID FROM ( select *,ROW_NUMBER() over (order by FirstID)as tt from tbs) AS A ORDER BY
--------------------01.向上查找所有父节点-----------------WITH TEMP AS (SELECT * FROM CO_Department WHERE ID=11 --表的IDUNION ALL SELECT T0.* FROM TEMP,CO_Department T0 WHERE TEMP.ParentID=T0.ID --父级ID==子级ID)SELECT * FROM TEMP; 如图:根据"测试组"查找所有父节点 执行结果
4.7 Design an algorithm and write code to find the first common ancestor of two nodes in a binary tree. Avoid storing additional nodes in a data structure. NOTE: This is not necessarily a binary search tree. LeetCode上的原题,请参见我之前的博客Lowest Common Ancest