为不定层级的树添加ancestor和depth列

# 需求：为不定层级的树添加ancestor和depth列

# 知识点：select后面根据指定的列再进行子查询
# 知识点：update后多张表，可以根据2张表的关系进行更新

# 查询某个节点的所有子孙节点
# 查询某个节点的所有上级节点和所有子孙节点

DROP TABLE IF EXISTS `category`;
CREATE TABLE `category` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `pid` int(11) NOT NULL,
  `ancestor` varchar(255) NOT NULL,
  `depth` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of category
-- ----------------------------
INSERT INTO `category` VALUES ('1', '家用电器', '0', '0', '1');
INSERT INTO `category` VALUES ('2', '手机数码', '0', '0', '1');
INSERT INTO `category` VALUES ('3', '电脑办公', '0', '0', '1');
INSERT INTO `category` VALUES ('4', '手机通讯', '2', '0,2', '2');
INSERT INTO `category` VALUES ('5', '手机配件', '2', '0,2', '2');
INSERT INTO `category` VALUES ('6', '智能设备', '2', '0,2', '2');
INSERT INTO `category` VALUES ('7', '手机壳', '5', '0,2,5', '3');
INSERT INTO `category` VALUES ('8', '贴膜', '5', '0,2,5', '3');


alter table category add column `ancestor` varchar(255) NOT NULL;
alter table category add column `depth` int(11) NOT NULL;
# 1级
select * from category
where pid = 0

update category set ancestor=0,depth = 1
where pid = 0

# 2级
select * from category
where pid in
(
select id from category
where pid = 0
)

# 查询出2级的祖先
select C1.*,
(select CONCAT(ancestor,',',id) from category C2 where C2.id = C1.pid)
as ancestor2 
from category as C1
where pid in
(
select id from category
where pid = 0
)
# 更新2级的祖先，将一张表的数据更新到另一张表

update category T1,
(
select C1.*,
(select CONCAT(ancestor,',',id) from category C2 where C2.id = C1.pid)
as ancestor2 
from category as C1
where pid in
(
select id from category
where pid = 0
)
) as T2
set T1.ancestor = T2.ancestor2, T1.depth = 2
where T1.id = T2.id

# 3级
select * from category
where pid in
(
select id from category
where pid in
(
select id from category
where pid = 0
)
)
# 查询3级的祖先
select C1.*,
(select CONCAT(ancestor,',',id) from category C2 where C2.id = C1.pid)
as ancestor2 
from category as C1
where pid in
(
select id from category
where pid in
(
select id from category
where pid = 0
)
)

# 更新3级的祖先
update category as T1,
(
select C1.*,
(select CONCAT(ancestor,',',id) from category C2 where C2.id = C1.pid)
as ancestor2 
from category as C1
where pid in
(
select id from category
where pid in
(
select id from category
where pid = 0
)
)
) as T2
set T1.ancestor = T2.ancestor2, T1.depth = 3
where T1.id = T2.id

# 4,5...级
# 根据上面的方法依次类推