递归查询

有时候表结构是层级关系的父子结构，要查出所有有的子，可用如下的sql，递归查询，以mysql为例：

1、查出父下所有子

WITH RECURSIVE producttype AS(
    SELECT '03f9096d-bd5d-11ed-a58a-7af8c5058daf' FinanClass, id,protypeid,typename,typelevel FROM t_base_commontype WHERE id='03f9096d-bd5d-11ed-a58a-7af8c5058daf' 
    UNION ALL
    SELECT '03f9096d-bd5d-11ed-a58a-7af8c5058daf' FinanClass,a.id,a.protypeid,a.typename,a.typelevel FROM t_base_commontype AS a,producttype AS b WHERE a.id = b.protypeid 
    )
         SELECT * FROM producttype  

得到的结果如下：

 2、还可以把查询的结果插入到临时表：

 CREATE TABLE #temp_producttype
                                        (
                                            FinanClass VARCHAR(50) ,
                                            id VARCHAR(36) ,
                                            protypeid VARCHAR(36),
                                                                                        typename VARCHAR(50),
                                                                                        typelevel VARCHAR(10)
                                        );

WITH RECURSIVE producttype AS(
    SELECT '03f9096d-bd5d-11ed-a58a-7af8c5058daf' FinanClass, id,protypeid,typename,typelevel FROM t_base_commontype WHERE id='03f9096d-bd5d-11ed-a58a-7af8c5058daf' 
    UNION ALL
    SELECT '03f9096d-bd5d-11ed-a58a-7af8c5058daf' FinanClass,a.id,a.protypeid,a.typename,a.typelevel FROM t_base_commontype AS a,producttype AS b WHERE a.id = b.protypeid 
    )
        insert into #temp_producttype  SELECT * FROM producttype  

 

3、将查询出来的值进行行转列

WITH RECURSIVE producttype AS(
    SELECT '03f9096d-bd5d-11ed-a58a-7af8c5058daf' FinanClass, id,protypeid,typename,typelevel FROM t_base_commontype WHERE id='03f9096d-bd5d-11ed-a58a-7af8c5058daf' 
    UNION ALL
    SELECT '03f9096d-bd5d-11ed-a58a-7af8c5058daf' FinanClass,a.id,a.protypeid,a.typename,a.typelevel FROM t_base_commontype AS a,producttype AS b WHERE a.id = b.protypeid 
    )
         SELECT FinanClass, GROUP_CONCAT(typename SEPARATOR '-') FROM producttype  where protypeid <>'00000000-0000-0000-0000-000000000000'  GROUP BY FinanClass;

 

4、将查询出来行转成多列

WITH RECURSIVE producttype AS(
    SELECT '03f9096d-bd5d-11ed-a58a-7af8c5058daf' FinanClass, id,protypeid,typename,typelevel FROM t_base_commontype WHERE id='03f9096d-bd5d-11ed-a58a-7af8c5058daf' 
    UNION ALL
    SELECT '03f9096d-bd5d-11ed-a58a-7af8c5058daf' FinanClass,a.id,a.protypeid,a.typename,a.typelevel FROM t_base_commontype AS a,producttype AS b WHERE a.id = b.protypeid 
    )
        
        select t.FinanClass,max(t.一级) '一级',max(t.二级) '二级',max(t.三级) '三级',max(t.四级) '四级' from 
         (SELECT FinanClass, case when typelevel=1 then typename else  '' end as  '一级',
                        case when typelevel=2 then typename else  '' end as  '二级',
                                            case when typelevel=3 then typename else  '' end as  '三级',
                                            case when typelevel=4 then typename else  '' end as  '四级'
                                    
                            
                    
                    FROM producttype  where protypeid <>'00000000-0000-0000-0000-000000000000' ) t  group by t.FinanClass