mysql 递归查询

-- https://blog.csdn.net/weixin_43292547/article/details/103866720
-- 小 > 大
set @a=6;
select @a as _id,(select @a:=a from s2 where id=_id) as pid
	from s2 where @a<>1;

-- 标签分类递归查询 小 > 大
set @id = 115 ;
select t1.* from tab_classify t1
inner join
(
    select @id as _id,
        (
            select @id := parent from tab_classify where id = _id
        ) as pid
    from tab_classify
    where @id is not null
) t3
on t1.id = t3._id
;

## 标签分类递归查询 大 > 小
-- mysql < 8.0  递归查询
-- https://www.cnblogs.com/blwy-zmh/p/13139641.html
SELECT * FROM tab_classify tt WHERE id IN
(
    SELECT id FROM tab_classify
        WHERE 1=1
        and id = @id
#         and name = 'xxx'
    UNION
    (
        SELECT id FROM
        (
            SELECT * FROM tab_classify ORDER BY parent,id
        ) depart_sorted,
        (
            SELECT @pv := @id
        ) initialisation
        WHERE find_in_set(parent,@pv)
        AND length(@pv := concat(@pv,',',id))
    )
)
# order by tt.parent
;


/*
  例子3:生成斐波那契数列
  version > 8.0
*/
WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
(
  SELECT 1, 0, 1
  UNION ALL
  SELECT n + 1, next_fib_n, fib_n + next_fib_n
    FROM fibonacci WHERE n < 10
)
SELECT * FROM fibonacci;