假设表t_menu为:

查询id为："333"的根节点的名字：

SELECT T2.name
FROM (   
    SELECT   
        @r AS _id,   
        (SELECT @r := parent_id FROM `t_menu` WHERE id = _id) AS 2v2,   
        @l := @l + 1 AS lvl   
        (SELECT @r := '333',@l :=0) vars, 
        t_menu h   
    WHERE @r is not null) T1   
left JOIN t_menu T2   
ON T1._id = T2.id
where T2.parent_id is null
查询id为："333"的所有父节点 且按从上到下排序
 
SELECT T2.*
FROM (   
    SELECT   
        @r AS _id,   
        (SELECT @r := parent_id FROM `t_menu` WHERE id = _id) AS 2v2,   
        @l := @l + 1 AS lvl   
        (SELECT @r := '333',@l :=0) vars, 
        t_menu h   
    WHERE @r is not null) T1   
left JOIN t_menu T2   
ON T1._id = T2.id
order by T1.lvl desc
	SELECT
		@r AS _id,
		( SELECT @r := parent_id FROM `look_project_process` WHERE id = _id ) AS 2v2,
		@l := @l + 1 AS lvl 
		( SELECT @r := 'e718165baece49678359dd945371f93e', @l := 0 ) vars,
		look_proje
                                    mysql 遍历查询所有父级：SELECT T1._id groupIDFROM (SELECT@r AS _id,(SELECT @r := fbh FROM business_group WHERE bh = _id) AS fbh,@l := @l   1 AS lvl FROM (SELECT @r := #{groupID}, @l := 0) vars,business_group h...
                                    I have a MySQL table with fields as below:id            name             parent1             Fruit             02             Meat              03             Orange            14             Beef    ...
                                    在 SQL Server 中，使用 CTE 表达式很容易做到无限层次父子关系查询；在不支持CTE表达式的版本中，借助函数递归也可以轻松实现。
在 MySQL 中，这个需求的实例稍显复杂， MySQL 中没有支持递归的查询，没有表值函数，函数不支持递归，所以通常都是用循环实现，显得比较别扭。今天看到一个用单条语句实现的递归查询，想法独特，分享一下。
表结构和数据
CREATE TABLE table1(id int, name varchar(10), parent_id int);
INSERT table
                                    面试官给我画个表 让我根据0找100 没见过世面的我一顿胡说八道 回来钻研了下正确的解决办法参考 https://blog.csdn.net/javadhh/article/details/48622743关键的点是1.while循环(我连mysql能while循环都不知道)2.把parentid复制给id去找id的parentid 直到返回结果为空 那这个就是根节点了为什么我得sql里要写...
                                    我不知道MYSQL是否可行,我的职业生涯主要是在SQL Server上工作.在SQL Server中,使用WITH语句可以只执行一次查询.这演示了如何在所有级别上获取对象的所有子代(id = 3)With pa as (select pa1.*From prarent as pa1Where id = 3union allselect pa2.*from pa join prarent as pa...
                                    前言：项目开发的过程中我留意到了其中一个页面的树的查询十分的慢，用户体验较差，于是便去看看原因。发现这里查找树的sql语句是这样的：select * from dept where find_in_set(id,getDeptChildList(1))getDeptChildList是函数，内容如下（navicat）： 
    参数：rootId VARCHAR(1000) 
    BEGIN
e.on_line,
e.name,
(SELECT d.longitude FROM sys_equipment_data d WHERE d.equipment_id = e.id ORDER BY d.transmission_time DESC LIMIT 1) AS longitude,
(SELECT d.latitude FROM sys_equipment...
                                    设置mysql外网可访问
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION; 
核心配置文件my.cnf
1.配置服务器字符集编码utf-8
character-server-set=utf8
日志文件log-bin*默认关闭
错误日志log-error*默认关闭
查询日...