SQL实现递归查询父子级信息

最新推荐文章于 2022-09-08 13:29:13 发布

原创最新推荐文章于 2022-09-08 13:29:13 发布 · 5k 阅读

6 ·

CC 4.0 BY-SA版权

笔记专栏收录该内容

7 篇文章

订阅专栏

本文介绍了如何使用SQL进行递归查询，以获取父子级信息。通过创建带有上下级关系的表，例如区划信息表，并利用递归公用表表达式(CTE)，可以实现根据子级ID查询父级的部门、区划和组织机构等信息。

SQL实现递归查询
根据父级的部门ID查询所有的子级部门；

根据父级的区划代码查询所属下级的所有区划信息；

根据子级ID查询隶属行政区划信息；

根据组织机构ID查询所有子级组织机构；

数据准备

DROP TABLE IF EXISTS `tb_area`;

CREATE TABLE tb_area(
  area_id int NOT NULL ,
  area_name varchar(255) DEFAULT NULL,
  area_code varchar(255) DEFAULT NULL,
  pid int DEFAULT NULL,
  PRIMARY KEY (area_id)
);

我们首先来创建一张拥有上下级关系的表，这里选择创建一张区划信息表，然后添加数据：

INSERT INTO tb_area VALUES(1, '中国', '100000', 0);
INSERT INTO tb_area VALUES (2, '广西壮族自治区', '450000000000', 1);
INSERT INTO tb_area VALUES (3, '防城港市', '450600000000', 2);
INSERT INTO tb_area VALUES (4, '港口区', '450602000000', 3);
INSERT INTO tb_area VALUES (5, '防城区', '450603000000', 3);
INSERT INTO tb_area VALUES (6, '上思县', '450621000000', 3);
INSERT INTO tb_area VALUES (7, '东兴市', '450681000000', 3);
INSERT INTO tb_area VALUES (8, '渔州坪街道', '450602001000', 4);
INSERT INTO tb_area VALUES (9, '白沙叶街道', '450602002000', 4);
INSERT INTO tb_area VALUES (10, '沙潭江街道', '450602003000', 4);
INSERT INTO tb_area VALUES (11, '王府街道', '450602004000', 4);
INSERT INTO tb_area VALUES (12, '企沙镇', '450602100000', 4);
INSERT INTO tb_area VALUES (13, '光坡镇', '450602101000', 4);
INSERT INTO tb_area VALUES (14, '水营街道', '450603001000', 5);
INSERT INTO tb_area VALUES (15, '珠河街道', '450603002000', 5);
INSERT INTO tb_area VALUES (109, '文昌街道', '450603003000', 5);
INSERT INTO tb_area VALUES (110, '大菉镇', '450603101000', 5);
INSERT INTO tb_area VALUES (111, '华石镇', '450603102000', 5);
INSERT INTO tb_area VALUES (112, '那梭镇', '450603103000', 5);
INSERT INTO tb_area VALUES (113, '那良镇', '450603104000', 5);
INSERT INTO tb_area VALUES (114, '峒中镇', '450603105000', 5);
INSERT INTO tb_area VALUES (115, '茅岭镇', '450603106000', 5);
INSERT INTO tb_area VALUES (116, '江山镇', '450603107000', 5);
INSERT INTO tb_area VALUES (117, '扶隆镇', '450603108000', 5);
INSERT INTO tb_area VALUES (118, '滩营乡', '450603204000', 5);
INSERT INTO tb_area VALUES (119, '十万山瑶族乡', '450603209000', 5);
INSERT INTO tb_area VALUES (120, '思阳镇', '450621100000', 6);
INSERT INTO tb_area VALUES (121, '在妙镇', '450621101000', 6);
INSERT INTO tb_area VALUES ('122', '华兰镇', '450621102000', 6);
INSERT INTO tb_area VALUES (123, '叫安镇', '450621103000', 6);
INSERT INTO tb_area VALUES (124, '南屏瑶族乡', '450621203000', 6);
INSERT INTO tb_area VALUES (125, '平福乡', '450621204000', 6);
INSERT INTO tb_area VALUES (126, '那琴乡', '450621206000', 6);
INSERT INTO tb_area VALUES (127, '公正乡', '450621208000', 6);
INSERT INTO tb_area VALUES (128, '东兴镇', '450681100000', 7);
INSERT INTO tb_area VALUES (129, '江平镇', '450681101000', 7);
INSERT INTO tb_area VALUES (130, '马路镇', '450681102000', 7);

在这里插入图片描述
代码编写

首先我们来写自下而上的查询：

也就是根据下级区划代码ID查询隶属行政区

创建一个函数：

drop function if exists getAreaChildBottomUp;
create function getAreaChildBottomUp(@areaId varchar(10))
returns @temp table(area_id int,area_name varchar(20),area_code varchar(20),pid varchar(10))
as
begin
	insert into @temp select area_id ,area_name,area_code,pid from tb_area where area_id=@areaId;
	while @@ROWCOUNT<>0
		insert into @temp
		select a.area_id ,a.area_name,a.area_code,a.pid 
		from tb_area a,@temp b
		where a.pid=b.area_id;
	end 
    return
END

select * from dbo.getAreaChildBottomUp(1);

用递归公用表表达式(CTE)

with aa 
as
(select area_id,area_name,area_code,pid from tb_area where area_id=122
union all 
select bb.* from tb_area bb inner join aa on bb.area_id=aa.pid)

select * from aa;