向下递归包过自身
SELECT
id
FROM
(
SELECT
a.id,
IF
//子集
( find_in_set( parent_id, @pid ) > 0, @pid := concat( @pid, ',', id ), 0 ) AS subset,
IF
//自身
( find_in_set( id, @pid ) > 0, @pid := concat( @pid, ',', id ), 0 ) AS oneself
FROM
( SELECT id, parent_id FROM etp_info WHERE del_flag = 0 ORDER BY parent_id, id ) a,
//条件
( SELECT @pid := 195 ) b
) c
WHERE
subset != 0
//将自身作为条件输入最终结果
OR oneself != 0
向上递归包过自身
SELECT
T2.id,
T2.etp_name
FROM
(
SELECT
@r AS _id,
( SELECT @r := parent_id FROM etp_info WHERE id = _id ) AS 2v2,
@l := @l + 1 AS lvl
FROM
( SELECT @r := 88 ) vars,
etp_info h
WHERE
@r <> 0
) T1
JOIN etp_info T2 ON T1._id = T2.id
ORDER BY
T2.id
mybatis 默认不支持这种自定义注解 只需要加一个 @SqlParser(filter = true) 加载mapper 层上就好了
@pid --> 这个表示变量名,如果你不加的话,会认为这是一个列名,但是这列不存在 ,具体操作可以查看官方文档说明
https://dev.mysql.com/doc/refman/5.7/en/user-variables.html
注意:
mysql中变量不用事前申明,在用的时候直接用“@变量名”使用就可以了。
第一种用法:set @num=1; 或set @num:=1; //这里要使用变量来保存数据,直接使用@num变量
第二种用法:select @num:=1; 或 select @num:=字段名 from 表名 where ……
注意上面两种赋值符号,使用set时可以用“=”或“:=”,但是使用select时必须用“:=赋值”
例如 用set
SET @pid = 195;
SELECT
id
FROM
(
SELECT
a.id,
IF
( find_in_set( parent_id, @pid ) > 0, @pid := concat( @pid, ',', id ), 0 ) AS subset,
IF
( find_in_set( id, @pid ) > 0, @pid := concat( @pid, ',', id ), 0 ) AS oneself
FROM
( SELECT id, parent_id FROM etp_info WHERE del_flag = 0 ORDER BY parent_id, id ) a
) c
WHERE
subset != 0
OR oneself != 0