简单记录下CTE的使用

with cte名称 as (
  query
)
select * from cte名称;


//递归
with recursive cte名称 as (
  anchor
  union all
  recursive member
)
select * from cte名称;

递归使用示例和说明：

WITH RECURSIVE cte_count (n) 
AS (
      SELECT 1
      UNION ALL
      SELECT n + 1 
      FROM cte_count 
      WHERE n < 3
    )
SELECT n 
FROM cte_count;

面包屑、分级菜单查询

-- 表结构
CREATE TABLE `category` (
  `id` bigint NOT NULL DEFAULT '' AUTO_INCREMENT COMMENT 'id',
  `category_name` varchar(64) NOT NULL DEFAULT '' COMMENT '类别名称',
  `category_grade` tinyint NOT NULL DEFAULT '0' COMMENT '类别等级',
  `parent_id` varchar(24) NOT NULL DEFAULT '0' COMMENT '类别父ID',
  `category_code` varchar(255) NOT NULL DEFAULT '' COMMENT '类别编码(每一级3位表示  如100000000,101001001)',
  `sort_no` int NOT NULL DEFAULT '0' COMMENT '排序',
  `gmt_create` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='分类信息表';

-- 查询id='1498106895829716992'所有上级菜单
with recursive category_path as 
(select * from category where id='1498106895829716992' 
 union select t1.* from category t1 inner join category_patht2 on t1.id=t2.parent_id where t1.category_grade>=0
) 
select * from category_path order by category_grade,id;

-- 查询id='1516374209020710914'所有下级菜单
with recursive category_path as 
(select * from category where id='1516374209020710914' 
union select t1.* from category t1 inner join category_patht2 on t2.id=t1.parent_id 
) 
select * from category_path order by category_grade,id;