以下是一种方式,可以使用MySQL中的递归查询来实现树形结构层级统计:
1.创建测试表格:
```
CREATE TABLE test (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(50) DEFAULT NULL,
parent_id int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO test (id,name,parent_id) VALUES (1,'A',0);
INSERT INTO test (id,name,parent_id) VALUES (2,'B',1);
INSERT INTO test (id,name,parent_id) VALUES (3,'C',1);
INSERT INTO test (id,name,parent_id) VALUES (4,'D',2);
INSERT INTO test (id,name,parent_id) VALUES (5,'E',2);
INSERT INTO test (id,name,parent_id) VALUES (6,'F',3);
INSERT INTO test (id,name,parent_id) VALUES (7,'G',3);
INSERT INTO test (id,name,parent_id) VALUES (8,'H',4);
```
2.执行以下查询语句:
```
SELECT
CONCAT(REPEAT(' ', COUNT(parent.name) - 1), node.name) AS name,
COUNT(parent.name) - 1 AS depth,
COUNT(*) AS count
FROM
test AS node,
test AS parent
WHERE
node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY
node.name
ORDER BY
node.lft;
```
结果将显示出每个节点的名称,深度以及其子节点的数量。可以根据需要调整查询内容和筛选条件以满足特定要求。