SQL生成树型结构数据

最新推荐文章于 2025-10-27 14:19:01 发布

原创最新推荐文章于 2025-10-27 14:19:01 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库

用标准SQL生成树型结构

# 使用条件
Mysql8.0以上使用
PostgreSQL 12.10
openGauss 5.0.0 
#####取节点下的所有子节点，包含自己
WITH RECURSIVE tree AS (
    SELECT id, name, parent_id FROM tableName WHERE id=42 -- 根节点条件    
    UNION ALL    
    SELECT t.id, t.name, t.parent_id
    FROM tableName t
    INNER JOIN tree ON t.parent_id = tree.id
)
SELECT * FROM tree;

#####取节点下的所有子节点，不包含自己
WITH RECURSIVE tree AS (
    SELECT id, name, parent_id FROM tableName WHERE parent_id=42 -- 根节点条件    
    UNION ALL    
    SELECT t.id, t.name, t.parent_id
    FROM tableName t
    INNER JOIN tree ON t.parent_id = tree.id
)
SELECT * FROM tree;

Oracle用SQL直接生成树型结构

    SELECT T.DID,
           CASE WHEN T.AREA_LEVEL=3 THEN  '|'||T.AREA_NAME
                WHEN T.AREA_LEVEL=4 THEN  ' |-'||T.AREA_NAME
                WHEN T.AREA_LEVEL=5 THEN  '  |-'||T.AREA_NAME END AREA_NAME,
           T.AREA_LEVEL,T.AREA_PARENT_DID,SYS_CONNECT_BY_PATH(t.AREA_NAME, '\') AS PATH_NAME
    FROM tableName T
    where 1 = 1
    CONNECT BY PRIOR t.DID = t.AREA_PARENT_DID
    START WITH T.AREA_LEVEL = 3
    order by PATH_NAME;

Mysql用SQL直接生成树型结构

#####取节点下的所有子节点，不包含自己
SELECT
    t3.category_id,t3.category_name,t3.parent_id,t3.dynamic_url
FROM
    (
			  SELECT t1.category_id,
					IF ( find_in_set( t1.parent_id, @parentIds ) > 0, @parentIds := concat( @parentIds, ',', t1.category_id ), 0 ) AS ischild ,
					t1.category_name,t1.dynamic_url,t1.parent_id
				FROM
						( SELECT category_id, parent_id,category_name,dynamic_url FROM sys_notice_category t WHERE t.del_flag = '0' and t.status='0'  ORDER BY parent_id, category_id ) t1,
						( SELECT @parentIds := '1' category_id ) t2 
    ) t3 
WHERE
    t3.ischild != 0;