mysql无限极递归查询

Bertha 。 2022-03-16 10:46 399阅读 0赞

sql递归查询出所有类目

select b.* from tbl_taoy_goods_cat b left join tbl_taoy_goods_cat a on b.father_id = a.num_id

java代码实现递归处理处理  
参考：[https://blog.csdn.net/u010820422/article/details/79239791][https_blog.csdn.net_u010820422_article_details_79239791]

/**
         * <p class="detail">
         * 功能：查询商品类目
         * </p>
         * @author Zerlinda
         * @date 2019年1月18日 
         * @param fatherId 父类ID，传入0时查询所有一级类目；传入非0时，查询二级类目；传入空时，查询所有类目
         * @return
         */
        @RequestMapping(value="/list.app", method={RequestMethod.POST, RequestMethod.GET}, produces = MediaType.APPLICATION_JSON_UTF8_VALUE)
        public Object getGoodsCatList(String fatherId){
            
            List<GoodsCat> list = goodsCatService.findByFatherId(fatherId);
            
            if(ExStringUtils.isBlank(fatherId)){
                list = goodsCatService.findAll();
                
                if(null != list && list.size() > 0){
                    List<TblTaoyGoodsCat> allGc = new ArrayList<TblTaoyGoodsCat>();
                    for(GoodsCat gc : list) { 
                        TblTaoyGoodsCat tgc = (TblTaoyGoodsCat)BeanCloneUtil.copyProperties(TblTaoyGoodsCat.class, gc);
                        allGc.add(tgc);
                    }
                    List<TblTaoyGoodsCat> tree = getAllGoodCatTree(allGc, "0");
                    return ResponseObj.success(MessagesResource.OPERATION_SUCCESS, tree);
                }
                
            }
            
            return ResponseObj.success(MessagesResource.OPERATION_SUCCESS, list);
        }

递归查询下级

private List<TblTaoyGoodsCat> getAllGoodCatTree(List<TblTaoyGoodsCat> allGc, String parentId){
            List<TblTaoyGoodsCat> parentGoodsCat = new ArrayList<TblTaoyGoodsCat>();
            List<TblTaoyGoodsCat> notParentGoodsCat = new ArrayList<TblTaoyGoodsCat>();
            // 找出所有根节点
            if(null != allGc && allGc.size() > 0){
                for(TblTaoyGoodsCat gc : allGc) { 
                    if(parentId.equals(gc.getFatherId())){
                        parentGoodsCat.add(gc);
                    }else {
                        notParentGoodsCat.add(gc);
                    }
                }
            }
            
            // 递归获取所有子节点
            if(parentGoodsCat.size() > 0){
                for(TblTaoyGoodsCat gc : parentGoodsCat) { 
                    gc.setSon(getAllGoodCatTree(notParentGoodsCat, gc.getNumId()+""));
                }
            }
            return parentGoodsCat;
        }

[https_blog.csdn.net_u010820422_article_details_79239791]: https://blog.csdn.net/u010820422/article/details/79239791