Mysql8.0 优雅查询无限级，父子级表

最新推荐文章于 2024-07-25 15:50:18 发布

Msx云南人

最新推荐文章于 2024-07-25 15:50:18 发布

阅读量1.3k

点赞数

分类专栏： mysql 文章标签： linux 数据库运维

本文链接：https://blog.csdn.net/qq_42402783/article/details/128221973

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

经常为一次想要查出所有子级分类，父级分类烦恼，用mysql8.0，with recursive 递归查询出所有关系分类

表结构

CREATE TABLE "t_category" (
  "cat_id" INTEGER NOT NULL,
  "name" TEXT,
  "parent_cid" INTEGER,
  PRIMARY KEY ("cat_id")
);

数据

INSERT INTO "main"."t_category" ("cat_id", "name", "parent_cid") VALUES (4, '服装', 0);
INSERT INTO "main"."t_category" ("cat_id", "name", "parent_cid") VALUES (5, '女装', 4);
INSERT INTO "main"."t_category" ("cat_id", "name", "parent_cid") VALUES (6, '男装', 4);
INSERT INTO "main"."t_category" ("cat_id", "name", "parent_cid") VALUES (7, '童装', 4);
INSERT INTO "main"."t_category" ("cat_id", "name", "parent_cid") VALUES (12, '美妆', 0);
INSERT INTO "main"."t_category" ("cat_id", "name", "parent_cid") VALUES (13, '护肤', 12);
INSERT INTO "main"."t_category" ("cat_id", "name", "parent_cid") VALUES (14, '化妆水', 13);
INSERT INTO "main"."t_category" ("cat_id", "name", "parent_cid") VALUES (15, '护肤套装', 13);
INSERT INTO "main"."t_category" ("cat_id", "name", "parent_cid") VALUES (16, '面膜', 13);
INSERT INTO "main"."t_category" ("cat_id", "name", "parent_cid") VALUES (17, '乳液面霜', 13);
INSERT INTO "main"."t_category" ("cat_id", "name", "parent_cid") VALUES (18, '彩妆', 12);
INSERT INTO "main"."t_category" ("cat_id", "name", "parent_cid") VALUES (19, '美容美体', 12);
INSERT INTO "main"."t_category" ("cat_id", "name", "parent_cid") VALUES (38, '润唇膏', 13);
INSERT INTO "main"."t_category" ("cat_id", "name", "parent_cid") VALUES (39, '卸妆', 13);
INSERT INTO "main"."t_category" ("cat_id", "name", "parent_cid") VALUES (40, '防晒', 13);

代码如下

-- 所有子分类
with recursive type_cte as (
    select *  from t_category  where cat_id = 12
    union all
    select t.* from t_category t
                        inner join type_cte type_cte2 on t.parent_cid = type_cte2.cat_id
)
select
    cat_id, name, parent_cid
from type_cte


-- 查询所有子分类，并带上上级名称
with recursive type_cte as (
    select cat_id,name,parent_cid  from t_category  where cat_id = 12
    union all
    select t.cat_id,type_cte2.name||'>'||t.name,t.parent_cid 
    from t_category t
        inner join type_cte type_cte2 on t.parent_cid = type_cte2.cat_id
)
select
    cat_id, name, parent_cid
from type_cte;

-- 查询所有父分类
with recursive type_cte as (
    select cat_id,name,parent_cid  from t_category  where cat_id = 40
    union all
    select t.cat_id,concat(type_cte2.name,'>',t.name),t.parent_cid
    from t_category t
             inner join type_cte type_cte2 on t.cat_id = type_cte2.parent_cid
)
select
    cat_id, name, parent_cid
from type_cte;