start with conntet by.. mysql 解决

最新推荐文章于 2023-04-22 22:44:10 发布

求各路英雄评论不要匆匆拿去

最新推荐文章于 2023-04-22 22:44:10 发布

阅读量2.2k

点赞数 2

分类专栏： sql

本文链接：https://blog.csdn.net/weixin_39592763/article/details/79746625

版权

sql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

             select id                             ,
                    area_code                      ,
                    area_name                      ,
                    parent_id                      ,
                    validate_state
             from t_ot_sys_area sa START WITH area_code= #{dto.areaCode}
             connect by  prior  sa.parent_id=sa.ID
             ORDER BY sa.id

改好的，支持mysql 的sql
             SELECT         
                    ID                             ,
                    AREA_CODE                      ,
                    AREA_NAME                      ,
                    PARENT_ID                      ,
                    VALIDATE_STATE
            From  
                (
                    SELECT * FROM   T_OT_SYS_AREA  
                     WHERE   area_code  like concat('%',#{areaCode},'%') 
                     AND FIND_IN_SET(id,  GETTOTSYSAREAPARENTLIST(#{id})) 
                )   a

创建的函数
BEGIN  
/*vmtree_info表 传入org_id值 获取其子节点至末尾*/
   DECLARE sChildList VARCHAR(10000);  
   DECLARE sChildTemp VARCHAR(1000);  

   SET sChildTemp =rootId;  

   WHILE sChildTemp IS NOT NULL DO  
     IF (sChildList IS NOT NULL) THEN   
        SET sChildList = CONCAT(sChildTemp,',',sChildList);   
     ELSE   
        SET sChildList = CONCAT(sChildTemp);   
     END IF;   

     SELECT GROUP_CONCAT(parent_id) INTO sChildTemp FROM T_OT_SYS_AREA WHERE FIND_IN_SET(ID, sChildTemp)>0;  
   END WHILE;  
   RETURN sChildList;  
END