来个大佬帮看看 MYSQL 中查询关系树的问题

[Grayan]

获取 tb_company_info 表中 companyname = 'top' 及其子树数据。

表：

CREATE TABLE `tb_company_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '公司自增 id',
  `companyname` varchar(100) COLLATE utf8_unicode_ci NOT NULL COMMENT '公司名称',
  `parent` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '上级公司',
  `companypid` int(11) NOT NULL DEFAULT '0' COMMENT '公司 Pid',
  `administratorid` int(11) NOT NULL COMMENT '管理员 Id',
  `username` varchar(100) COLLATE utf8_unicode_ci NOT NULL COMMENT '用户名',
  `devmaxnumber` int(11) NOT NULL COMMENT '最大设备数量',
  `operator` varchar(100) COLLATE utf8_unicode_ci NOT NULL COMMENT '操作人',
  `operatetime` datetime NOT NULL COMMENT '操作时间',
  `operatorip` varchar(30) COLLATE utf8_unicode_ci NOT NULL COMMENT '操作 IP',
  PRIMARY KEY (`id`),
  UNIQUE KEY `companyName` (`companyname`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2395 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `httpserver`.`tb_company_info`(`id`, `companyname`, `parent`, `companypid`, `administratorid`, `username`, `devmaxnumber`, `operator`, `operatetime`, `operatorip`) VALUES (490, 'MR98C-B1', 'top', 0, 0, 'MR98CB1', 20, 'admin', '2021-12-17 18:22:39', '127.0.0.1');
INSERT INTO `httpserver`.`tb_company_info`(`id`, `companyname`, `parent`, `companypid`, `administratorid`, `username`, `devmaxnumber`, `operator`, `operatetime`, `operatorip`) VALUES (491, 'MR98E-B1', 'top', 0, 0, 'MR98EB1', 60, 'admin', '2021-12-17 18:22:39', '127.0.0.1');
INSERT INTO `httpserver`.`tb_company_info`(`id`, `companyname`, `parent`, `companypid`, `administratorid`, `username`, `devmaxnumber`, `operator`, `operatetime`, `operatorip`) VALUES (492, 'MR98E-B1-V101', 'MR98E-B1', 0, 0, 'MR98EB1V101', 20, 'admin', '2021-12-17 18:22:39', '127.0.0.1');
INSERT INTO `httpserver`.`tb_company_info`(`id`, `companyname`, `parent`, `companypid`, `administratorid`, `username`, `devmaxnumber`, `operator`, `operatetime`, `operatorip`) VALUES (513, 'top', '', 0, 0, 'yanfa', 100, 'admin', '2021-12-17 18:22:39', '127.0.0.1');

注意表中 parent 字段的值是 companyname

GPT 的答案如下，但是查不出数据，求教下原因以及该怎么修复

WITH RECURSIVE company_tree AS (
    SELECT id, companyname, parent
    FROM tb_company_info
    WHERE companyname = 'top'  
    UNION ALL
    SELECT c.id, c.companyname, c.parent
    FROM tb_company_info c
    INNER JOIN company_tree ct ON ct.companyname = c.parent 
)
SELECT * 
FROM company_tree;

[asmile1993]

版本：mysql 8.0.33 ，我能查到结果，楼主什么数据都查不出来吗？

mysql> WITH RECURSIVE company_tree AS (
-> SELECT id, companyname, parent
-> FROM tb_company_info
-> WHERE companyname = 'top'
-> UNION ALL
-> SELECT c.id, c.companyname, c.parent
-> FROM tb_company_info c
-> INNER JOIN company_tree ct ON ct.companyname = c.parent
-> )
-> SELECT *
-> FROM company_tree;
+------+---------------+----------+
| id | companyname | parent |
+------+---------------+----------+
| 513 | top | |
| 490 | MR98C-B1 | top |
| 491 | MR98E-B1 | top |
| 492 | MR98E-B1-V101 | MR98E-B1 |
+------+---------------+----------+

[RichardX2023]

WITH RECURSIVE 在 MYSQL 8.0 版本才开始支持，低版本会报语法错误吧 (●'◡'●)

[Grayan]

@asmile1993 #1 是版本问题，谢谢回复