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

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;

MySQL

查询

关系树

3 条回复 2024-05-11 21:26:42 +08:00

asmile1993

2024-05-09 17:54:35 +08:00

版本：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 |
+------+---------------+----------+

2/div>

RichardX2023

2024-05-09 18:39:26 +08:00

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

Grayan

2024-05-11 21:26:42 +08:00

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