Mysql树形结构表操作

最新推荐文章于 2024-07-06 09:22:27 发布

lancetop-stardrms

最新推荐文章于 2024-07-06 09:22:27 发布

阅读量466

点赞数

分类专栏：游戏开发文章标签： mysql

本文链接：https://blog.csdn.net/weixin_41645983/article/details/84323057

版权

游戏开发专栏收录该内容

1 篇文章 0 订阅

订阅专栏

sql语句：

# 查询所有叶子节点
select id from t_area where id not in (select p_id from t_area);
-- select areaname from sm_area where id not in (select parent_id from sm_area);

# 查询节点所有父节点
select
	@v as _id,
	(select @v := p_id from t_area where id = _id) as _pid,
	(select @l := @l + 1) as _lvl
from
	(select @v := 8, @l := -1) vars,
	t_area
where
	@v != -1;

# 查询所有子节点
/*SELECT 
areaname
from(
SELECT
	id,
	areaname,
	IF( find_in_set( parent_id, @pids ) > 0, @pids := concat( @pids, ',', id ), 0 ) AS ischild 
FROM
	sm_area,
	( SELECT @pids := 320000 ) t2) t3 WHERE ischild != 0;*/
select
	id
from(
	select 
		id,
		if(find_in_set(p_id, @pids) > 0, @pids := concat( @pids, ',', id), 0) as ischild
	from
		t_area,
		(select @pids := 1) T2
) T1
where ischild != 0;

表结构：

-- ----------------------------
-- Table structure for t_area
-- ----------------------------
DROP TABLE IF EXISTS `t_area`;
CREATE TABLE `t_area`  (
  `id` int(11) NOT NULL,
  `p_id` int(11) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of t_area
-- ----------------------------
INSERT INTO `t_area` VALUES (0, -1);
INSERT INTO `t_area` VALUES (1, 0);
INSERT INTO `t_area` VALUES (2, 0);
INSERT INTO `t_area` VALUES (3, 0);
INSERT INTO `t_area` VALUES (4, 1);
INSERT INTO `t_area` VALUES (5, 1);
INSERT INTO `t_area` VALUES (6, 2);
INSERT INTO `t_area` VALUES (7, 3);
INSERT INTO `t_area` VALUES (8, 4);
INSERT INTO `t_area` VALUES (9, 8);
INSERT INTO `t_area` VALUES (10, 9);

SET FOREIGN_KEY_CHECKS = 1;

树形图：