SQL查询技术详解：层级数据结构操作-优快云博客

本文链接：https://blog.youkuaiyun.com/wj196/article/details/84322894

本文深入探讨了SQL查询中如何操作层级数据结构，包括查询顶级父节点、查询子级节点以及查找父节点的功能。通过实例解析，详细介绍了使用SQL语句实现层级数据查询的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

district_upper 为父节点id，



/**从数据'夏津县'的id开始，根据他的district_upper找到以district_upper作为id的数据也就是找到他的父亲，查出所有的父级的数据*/
select t.id, t.district_name, t.district_upper
  from itcpn_district t
 start with id =
            (select id from itcpn_district where district_name = '夏津县')
connect by prior district_upper = id;

[img]http://dl.iteye.com/upload/attachment/0069/3187/ac71452c-d374-38c5-807a-67bdd841389d.jpg[/img]



/***从数据'山东省'的id开始，根据山东省的id找到以它作为父节点district_upper的数据，也就是找到他的孩子，查出所有的子级的数据*/
select t.id, t.district_name, t.district_upper
  from itcpn_district t
 start with id =
            (select id from itcpn_district where district_name = '山东省')
connect by district_upper = prior id;

[img]http://dl.iteye.com/upload/attachment/0069/3192/e661fb1a-e63c-3308-9c7a-b5404f9820cf.jpg[/img]

查询顶级父节点：


select t.id, t.district_name, t.district_upper
  from itcpn_district t 
  where t.district_upper is null 
 start with id =
            (select id from itcpn_district where district_name = '夏津县')
connect by prior district_upper = id;

[img]http://dl.iteye.com/upload/attachment/0069/3189/64ef83a8-8d00-313b-b1f4-49ae30b8cb00.jpg[/img]

学习参考：http://wentao365.iteye.com/blog/606009