mysql 检索所有子类别- sql查询

huwehgph  于 2022-11-21  发布在  Mysql
关注(0)|答案(3)|浏览(206)

此bounty已结束。回答此问题可获得+250声望奖励。奖励宽限期将在12小时后结束。StormTrooper希望吸引更多人关注此问题。

我将类别存储在单个表中。
对孩子的数量没有限制。
我想获取所提供类别ID的所有链接子类别：
获取层次结构的原因是，我需要更新每个新创建或更新的类别的路径字段。
表名：categories

id  parentId    name    path                         
A1   null       Cat 1   Cat 1                   
A2   A1         Cat 2   Cat 1 > Cat 2           
A3   A2         Cat 3   Cat 1 > Cat 2 > Cat 3   
A4   null       Cat A   Cat A                   
A5   A4         Cat B   Cat A > Cat B

现在，我想获取id: 1的层次结构
到目前为止，我所尝试的是：

with recursive cte (id, name, parentId) AS (
    select
        id,
        name,
        parentId
    from
        categories
    where
        parentId = 'A1'
    union
    all
    select
        c.id,
        c.name,
        c.parentId
    from
        categories c
        inner join cte on c.parentId = cte.id
)
select
    *
from
    cte;

上述查询返回：

[
    {
        id: A1,
        parentId: null,
        name: Cat 1,
        path: Cat 1
    },
    {
        id: A2,
        parentId: A1,
        name: Cat 2,
        path: Cat 1 > Cat 2
    }
]

但我想要这个：

[
    {
        id: A2,
        parentId: A1,
        name: Cat 2,
        path: Cat 1 > Cat 2
    },
    {
        id: A3,
        parentId: A2,
        name: Cat 3,
        path: Cat 1 > Cat 2 > Cat 3
    }
]

如果我提供id: 2，在这种情况下，我期望：

[
    {
        id: A3,
        parentId: A2,
        name: Cat 3,
        path: Cat 1 > Cat 2 > Cat 3
    }
]

我的查询有问题，有人能指出吗？
以下是重现的场景：https://dbfiddle.uk/Beefs-UH
重要提示：主关键字（即id）是唯一标识符字符串，而不是整数。因此无法按id对记录进行排序。

mysql

3条答案

按热度按时间
rlcwz9us

rlcwz9us1#

要获取上层次结构，请尝试以下操作：

with recursive cte (id, name, parentId, path, ord) as 
(
  select id, name, parentId, path, 1 as ord
  from  categories
  where id = 'A2'
  union all
  select c.id, c.name, c.parentId, c.path, t.ord+1
  from categories c join cte t
  on t.parentId = c.id 
)
select * from cte
order by ord desc;

这里，我们按生成的'ord'值对结果进行排序，不需要按id排序。
若要取得下层阶层，请使用下列程式码：

with recursive cte (id, name, parentId, path, ord) as 
(
  select id, name, parentId, path, 1 as ord
  from  categories
  where id = 'A2'
  union all
  select c.id, c.name, c.parentId, c.path, t.ord+1
  from categories c join cte t
  on c.parentId = t.id 
)
select * from cte
order by ord;

要获得完整的层次结构（向上和向下），可以将两个递归查询的结果合并为：

with recursive cte(id, name, parentId, path, ord) as 
(
  select id, name, parentId, path, 1 as ord
  from  categories
  where id = 'A2'
  union all
  select c.id, c.name, c.parentId, c.path, t.ord+1
  from categories c join cte t
  on t.parentId = c.id 
),
cte2(id, name, parentId, path, ord) as 
(
  select id, name, parentId, path,0 as ord 
  from  categories
  where id = 'A2'
  union all
  select c.id, c.name, c.parentId, c.path, ord-1
  from categories c join cte2 t
  on c.parentId = t.id 
)
select id, name, parentId, path from
(
  select *  from cte
  union all
  select *  from cte2
) T
where ord <> 0 /*to avoid id = 'A2' duplication (one from cte and one from cte2)*/
order by ord desc

由于要存储每个ID的完整路径，因此可以尝试使用自连接的另一种方法，如下所示：
对于上层：

select C1.id, C1.parentId, C1.name, C1.path
from categories C1 join categories C2
on C2.path like CONCAT('%', C1.name, '%')
where C2.id='A2'
order by length(C1.path)

这里，我们使用length(path)对结果进行排序，这将保证最上面的父节点首先出现，最下面的子节点最后出现。
对于下层，请使用on C1.path like CONCAT('%', C2.name, '%')。
对于完整层次结构，请使用on C1.path like CONCAT('%', C2.name, '%') or C2.path like CONCAT('%', C1.name, '%')。
请参见demo on MySQL。
请参阅demo on SQL Server（具有较小修改）。

赞(0）分享  回复(0）举报  2022-11-21
6jjcrrmo

6jjcrrmo2#

通过对“parentId”列应用筛选器，您从结果集中遗漏了根节点。（您的根没有父节点。）
下面是一个行之有效的方法：
1.使用所需的id过滤初始步骤。（我使用了第一个示例，id = 3）
1.沿着“父链”直到到达根。（Cat 3的父链是Cat 2，Cat 2的父链是Cat 1，Cat 1没有父链：已到达根目录。）
1.运行一个简单的选择。

WITH RECURSIVE cte (id, name, parentId, path, lvl) AS (
  -- Initial step
  SELECT
     id,
     name,
     parentId,
     path,
     1
  FROM categories
  WHERE id = 3

  UNION ALL

  -- Follow the "parent-chain"
  SELECT
     cat.id,
     cat.name,
     cat.parentId,
     cat.path,
     cte.lvl + 1
  FROM cte
  INNER JOIN categories cat
    ON cte.parentId = cat.id
)

SELECT id, name, parentId, path
FROM cte
ORDER BY lvl DESC
;

但是，由于您希望维护“path”字段，因此使用递归CTE计算所有路径，然后检查是否存在不匹配，这样可能更容易：

WITH RECURSIVE cte (root, id, name, parentId, old_path, new_path, lvl) AS (
  SELECT
      id,
      id,
      name,
      parentId,
      path,
      name,
      1
  FROM categories
  WHERE parentId = 0

  UNION ALL

  SELECT
      cte.root,
      cat.id,
      cat.name,
      cat.parentId,
      cat.path,
      CONCAT(cte.name, ' > ', cat.name),
      cte.lvl + 1
  FROM cte
  INNER JOIN categories cat
    ON cte.id = cat.parentId
)
  
SELECT *
FROM cte
WHERE old_path != new_path
ORDER BY root, lvl
;

编辑后问题的解决方案：

WITH RECURSIVE cte (id, name, parentId, path, lvl) AS (
  SELECT
     id,
     name,
     parentId,
     path,
     1
  FROM categories
  WHERE id = 2

  UNION ALL

  SELECT
     cat.id,
     cat.name,
     cat.parentId,
     cat.path,
     cte.lvl + 1
  FROM cte
  INNER JOIN categories cat
    ON cte.id = cat.parentId
)

SELECT id, name, parentId, path
FROM cte
ORDER BY lvl ASC
;

https://dbfiddle.uk/agO_kNXf

赞(0）分享  回复(0）举报  2022-11-21
1dkrff03

1dkrff033#

categories表中的数据表示由节点（类别）连接的树形结构：
1.节点可以具有零个或多个子节点;和
1.节点可以具有零个或一个父节点。
步骤1.创建表模式（稍作修改）

-- create table
create table tree (
    id          int,
    parent_id   int,
    name        varchar(100),
    path        varchar(200)
);

步骤2.生成测试数据，每个节点可以有0到3个子节点（请随意修改CTE以包括更多的union all。

insert into tree
with recursive cte (parent_id, id, name, path) as (
    select 0                      as parent_id, 
           1                      as id,
           convert(1, char(100))  as name,
           convert(1, char(100))  as path
    union all
    select id                     as parent_id,
           id*10 + 1              as id,
           convert(id*10+1, char) as name,
           concat(path,'>',convert(id*10+1, char)) as path
      from cte
     where id < 1000
       and floor(rand()*2) = 1
    union all
    select id                     as parent_id,
           id*10 + 2              as id,
           convert(id*10+2, char) as name,
           concat(path,'>',convert(id*10+2, char)) as path
      from cte
     where id < 1000
       and floor(rand()*2) = 1
    union all
    select id                     as parent_id,
           id*10 + 3              as id,
           convert(id*10+3, char) as name,
           concat(path,'>',convert(id*10+3, char)) as path
      from cte
     where id < 1000
       and floor(rand()*2) = 1
)
select id, parent_id, name, path
  from cte
 order by 1,2;

我的测试数据看起来像这样（YMMV，因为兰德（）函数）：

id  |parent_id|name|path         |
----+---------+----+-------------+
   1|        0|1   |1            |
  11|        1|11  |1>11         |
  12|        1|12  |1>12         |
  13|        1|13  |1>13         |
 112|       11|112 |1>11>112     |
 121|       12|121 |1>12>121     |
 122|       12|122 |1>12>122     |
 123|       12|123 |1>12>123     |
 132|       13|132 |1>13>132     |
 133|       13|133 |1>13>133     |
1121|      112|1121|1>11>112>1121|
1211|      121|1211|1>12>121>1211|
1221|      122|1221|1>12>122>1221|
1222|      122|1222|1>12>122>1222|
1233|      123|1233|1>12>123>1233|
1321|      132|1321|1>13>132>1321|
1332|      133|1332|1>13>133>1332|
1333|      133|1333|1>13>133>1333|

树的可视化如下所示：

步骤3.检索所有链接的父节点和子节点（即ID=12）

-- get all linked parent & child nodes
with recursive cte_tree_parent (id, parent_id, name, path) as (
    select id, 
           parent_id,
           name,
           path
      from tree
     where id = 12
     union all
    select p.id,
           p.parent_id,
           p.name,
           p.path
      from cte_tree_parent c  
      join tree p      
        on c.parent_id = p.id),
cte_tree_child (id, parent_id, name, path) as (
    select id, 
           parent_id,
           name,
           path
      from tree
     where id = 12
     union all
    select c.id,
           c.parent_id,
           c.name,
           c.path
      from cte_tree_child p  
      join tree c      
        on p.id = c.parent_id)
select id, parent_id, name, path
  from cte_tree_parent
 union
select id, parent_id, name, path
  from cte_tree_child
 order by path;

结果：

id  |parent_id|name|path         |
----+---------+----+-------------+
   1|        0|1   |1            |
  12|        1|12  |1>12         |
 121|       12|121 |1>12>121     |
1211|      121|1211|1>12>121>1211|
 122|       12|122 |1>12>122     |
1221|      122|1221|1>12>122>1221|
1222|      122|1222|1>12>122>1222|
 123|       12|123 |1>12>123     |
1233|      123|1233|1>12>123>1233|
赞(0）分享  回复(0）举报  2022-11-21
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com