mysql查询上下级

##查询所有上级

with cte_organ(organ_id, organ_pre_id, organ_no, organ_name, organ_type, organ_level, proj_id, enterprise_id, order_id,organ_relation,source_organ_id)  
        as  
        (  
            select organ_id, organ_pre_id, organ_no, organ_name, organ_type, organ_level, proj_id, enterprise_id, order_id,organ_relation,source_organ_id  
            from d_sys_organ  
            where organ_id = 73 and deleted_flag = 0
            union all  
            select a.organ_id, a.organ_pre_id, a.organ_no, a.organ_name, a.organ_type, a.organ_level, a.proj_id, a.enterprise_id, a.order_id,a.organ_relation,a.source_organ_id  
            from d_sys_organ a  
            inner join   
            cte_organ b
            on a.organ_id=b.organ_pre_id  where a.deleted_flag = 0   
        )                         
        select organ_id, organ_pre_id, organ_no, organ_name, organ_type, organ_level, proj_id, enterprise_id, order_id,organ_relation,source_organ_id  from cte_organ order by organ_id
        
        
        
        
with test_student(stu_id, stu_name)  
as  
(  
    select stu_id, stu_name
    from sys_student  
    where school_id = '1' and del_flag = 0
    union all  
    select a.parent_id, a.stu_id, a.stu_name
    from sys_student a  
    inner join   
    test_student b
    on a.stu_id=b.parent_id  where a.del_flag = 0   
)                         
select stu_id, stu_name  from test_student order by stu_id

##查询所有下级

with cte_organ(organ_id, organ_pre_id, organ_no, organ_name, organ_type, organ_level, proj_id, enterprise_id, order_id,organ_relation,source_organ_id)  
        as  
        (  
            select organ_id, organ_pre_id, organ_no, organ_name, organ_type, organ_level, proj_id, enterprise_id, order_id,organ_relation,source_organ_id  
            from d_sys_organ  
            where organ_id = 3 and deleted_flag = 0
            union all  
            select a.organ_id, a.organ_pre_id, a.organ_no, a.organ_name, a.organ_type, a.organ_level, a.proj_id, a.enterprise_id, a.order_id,a.organ_relation,a.source_organ_id  
            from d_sys_organ a  
            inner join   
            cte_organ b
            on a.organ_pre_id=b.organ_id  where a.deleted_flag = 0 
        )                         
        select organ_id, organ_pre_id, organ_no, organ_name, organ_type, organ_level, proj_id, enterprise_id, order_id,organ_relation,source_organ_id  from cte_organ order by organ_id 
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        WITH cte_jlgl(periodId, periodName, thisSum, zhtbl)
        AS(
            SELECT pd.PERIOD_ID AS periodId,pd.PERIOD_NAME AS periodName, ISNULL((zf.this_sum)/10000, 0) AS thisSum,
                (ISNULL(zf.this_sum, 0))/(SELECT dbo.fnzero(ISNULL(htzj,0)) FROM d_ht_htgk WHERE organ_id = #{organId}) AS zhtbl
            FROM d_ht_period pd
            LEFT JOIN d_gcjl_hz_zqzf zf ON pd.ORGAN_ID = zf.organ_id AND pd.PERIOD_ID = zf.period_id AND zf.item_id = 230 AND zf.data_mark = 9
            WHERE pd.ORGAN_ID = #{organId}
        )
        SELECT periodId, periodName,
            CASE dbo.fnzero(ISNULL(thisSum, 0)) WHEN '' THEN 0 WHEN NULL THEN 0 ELSE CONVERT(DECIMAL(20, 2), dbo.fnzero(ISNULL(thisSum, 0))) END AS thisSum,
            CASE dbo.fnzero(ISNULL(zhtbl, 0)) WHEN '' THEN 0 WHEN NULL THEN 0 ELSE CONVERT(DECIMAL(20, 2), dbo.fnzero(ISNULL(zhtbl, 0))) END AS zhtbl
        FROM cte_jlgl
        UNION ALL
        SELECT NULL AS periodId, '合计' AS periodName, SUM(CONVERT(DECIMAL(20, 2), thisSum)) AS thisSum, SUM(CONVERT(DECIMAL(20, 2), zhtbl)) AS zhtbl FROM cte_jlgl
        ORDER BY periodId ASC