数据库递归查询sql语句

最新推荐文章于 2024-03-23 08:55:25 发布

家门口的二大爷

最新推荐文章于 2024-03-23 08:55:25 发布

阅读量447

点赞数 1

分类专栏：数据库文章标签：数据库 sql mysql

本文链接：https://blog.csdn.net/moijsda/article/details/121162902

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

本文介绍了在MySQL、SQL Server和Oracle数据库中进行递归查询的不同SQL方法，包括CTE（公共表表达式）和自连接等技术，以遍历具有层级关系的数据，例如菜单结构。提供了具体的建表和插入数据的例子，以及多种查询子菜单的SQL语句，帮助读者理解和应用递归查询。

摘要由CSDN通过智能技术生成

mysql数据库

建表语句：

CREATE TABLE `menu`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '菜单id',
  `parent_id` int(11) NULL DEFAULT NULL COMMENT '父节点id',
  `menu_name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '菜单名称',
  `menu_url` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '菜单路径',
  `status` tinyint(3) NULL DEFAULT 1 COMMENT '菜单状态 1-有效；0-无效',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 12213 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of menu
-- ----------------------------
INSERT INTO `menu` VALUES (1, 0, '菜单1', '', 1);
INSERT INTO `menu` VALUES (11, 1, '菜单11', '', 1);
INSERT INTO `menu` VALUES (12, 1, '菜单12', '', 1);
INSERT INTO `menu` VALUES (13, 1, '菜单13', '', 1);
INSERT INTO `menu` VALUES (111, 11, '菜单111', '', 1);
INSERT INTO `menu` VALUES (121, 12, '菜单121', '', 1);
INSERT INTO `menu` VALUES (122, 12, '菜单122', '', 1);
INSERT INTO `menu` VALUES (1221, 122, '菜单1221', '', 1);
INSERT INTO `menu` VALUES (1222, 122, '菜单1222', '', 1);
INSERT INTO `menu` VALUES (12211, 1222, '菜单12211', '', 1);
INSERT INTO `menu` VALUES (12212, NULL, '菜单0', ' ', 1);

SET FOREIGN_KEY_CHECKS = 1;

方法1：

select id from (
  select t1.id,
  if(find_in_set(parent_id, @pids) > 0, @pids := concat(@pids, ',', id), 0) as ischild
  from (
	   select id,parent_id from menu t where t.status = 1 order by parent_id, id
	  ) t1,
	  (select @pids := (select 12 ) ) t2
 ) t3 where ischild != 0

方法2：

SELECT id FROM
  (
    SELECT * FROM menu where parent_id in( 1) ORDER BY parent_id, id DESC
  ) realname_sorted,
  (SELECT @pv :=1) initialisation
  WHERE (FIND_IN_SET(parent_id,@pv)>0 And @pv := concat(@pv, ',', id))

方法3：

SELECT T2.level_, T3.* 
  FROM( 
	SELECT @codes as _ids, 
			( SELECT @codes := GROUP_CONCAT(id) 
				FROM menu 
			   WHERE FIND_IN_SET(parent_id, @codes) 
			) as T1, 
		   @l := @l+1 as level_ 
	  FROM menu, 
		  (SELECT @codes :=(select 1 '1' union all select 121 '1') , @l := 0 ) T4
	 WHERE @codes IS NOT NULL 
	   ) T2, menu T3 
  WHERE FIND_IN_SET(T3.id, T2._ids) 
  ORDER BY level_, id

方法4：

delimiter $$ 
drop function if exists get_child_list$$ 
create function get_child_list(in_id varchar(10)) returns varchar(1000) 
begin 
 declare ids varchar(1000) default ''; 
 declare tempids varchar(1000); 
 
 set tempids = in_id; 
 while tempids is not null do 
  set ids = CONCAT_WS(',',ids,tempids); 
  select GROUP_CONCAT(id) into tempids from menu where FIND_IN_SET(parent_id,tempids)>0;  
 end while; 
 return ids; 
end  
$$ 
delimiter ; 

select * from menu where FIND_IN_SET(id,get_child_list('122,121'))

sqlserver数据库

;with f as 
(
select * from menu where id in(1,121)
union all
select a.* from menu as a inner join f as b on a.parent_id=b.id
)
select DISTINCT ID from f;

ORACLE数据库


select a.pk_org, a.code, a.name, a.pk_fatherorg
 from org_orgs a
 start with a.code = '010201'
 connect by prior a.pk_org = a.pk_fatherorg
order by a.code