sql和mysql递归查询子节点_JAVA_编程开发_程序员俱乐部

中国优秀的程序员网站程序员频道CXYCLUB技术地图
热搜:
更多>>
 
您所在的位置: 程序员俱乐部 > 编程开发 > JAVA > sql和mysql递归查询子节点

sql和mysql递归查询子节点

 2011/12/23 9:32:15  fuaotech  http://fuaotech.iteye.com  我要评论(0)
  • 摘要://SQLSqlServer:CTE函数处理递归(WITH语法)cte可以其他名字注意匹配(所有cte全部替换)withcteas(selecta.typeid,a.superid,a.typenamefromctypeawheretypeid=1unionallselectk.typeid,k.superid,k.typenamefromctypekinnerjoincteconc.typeid=k.superid
  • 标签:SQL MySQL 递归
//SQL SqlServer:CTE函数处理递归(WITH语法)  cte可以其他名字 注意匹配(所有cte全部替换)
with cte as 
 ( 
 select a.typeid,a.superid,a.typename from ctype a where typeid=1
 union all  
 select k.typeid,k.superid,k.typename  from ctype k inner join cte c on c.typeid = k.superid 
 )select * from cte 
//MYSQL 放到命令行执行
CREATE FUNCTION `getChildDeptLst`(rootId INT)
    RETURNS varchar(1000)
    BEGIN
     DECLARE sTemp VARCHAR(1000);
     DECLARE sTempChd VARCHAR(1000);
    
    SET sTemp = '$';
    SET sTempChd =cast(rootId as CHAR);
   WHILE sTempChd is not null DO
  SET sTemp = concat(sTemp,',',sTempChd);
    SELECT group_concat(id) INTO sTempChd FROM department  where FIND_IN_SET(parentId,sTempChd)>0;
    END WHILE;
  RETURN sTemp;
END

select getChildLst(1);

select * from departmentg   where FIND_IN_SET(id, getChildDeptLst(1));
//
//查询子项父id
SELECT T2.id, T2 .name  FROM (  SELECT   @r AS _id,  (SELECT @r := parentId FROM department WHERE id = _id) AS parentId,  @l := @l + 1 AS lvl  FROM  (SELECT @r := 30, @l := 0) vars,  department h  WHERE @r <> 0) T1  JOIN department T2  ON T1._id = T2.id  ORDER BY T1.lvl DESC 

发表评论
用户名: 匿名