获取 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;
1
asmile1993 228 天前
版本: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
RichardX2023 228 天前 1
WITH RECURSIVE 在 MYSQL 8.0 版本才开始支持,低版本会报语法错误吧 (●'◡'●)
|
3
Grayan OP @asmile1993 #1 是版本问题,谢谢回复
|