V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
Grayan
V2EX  ›  MySQL

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

  •  
  •   Grayan · 44 天前 · 973 次点击
    这是一个创建于 44 天前的主题,其中的信息可能已经有所发展或是发生改变。

    获取 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;
    
    3 条回复    2024-05-11 21:26:42 +08:00
    asmile1993
        1
    asmile1993  
       44 天前
    版本: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
        2
    RichardX2023  
       44 天前   ❤️ 1
    WITH RECURSIVE 在 MYSQL 8.0 版本才开始支持,低版本会报语法错误吧 (●'◡'●)
    Grayan
        3
    Grayan  
    OP
       42 天前
    @asmile1993 #1 是版本问题,谢谢回复
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2093 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 01:41 · PVG 09:41 · LAX 18:41 · JFK 21:41
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.