父级 SQL 查询数量

发布于 2024-08-20 05:02:37 字数 423 浏览 6 评论 0原文

我的数据库中有一个名为 type 的表，

ID  Name     ParentID
---------------------
1   name1    0
2   name2    0
3   name3    1
4   name4    2
5   name1    1

我需要知道每种类型有多少个父级（后代）

ID --------后代

ID-> 1   (have no parent)
ID-> 3   (have 1 parent (ID->1))
ID-> 5   (have two parent ((ID->3(ID->1))))

我如何编写优化的 sql 语句来执行此操作使用MySQL？

原文

I have a table in my database called type

ID  Name     ParentID
---------------------
1   name1    0
2   name2    0
3   name3    1
4   name4    2
5   name1    1

I need to know how many parent (descendants) each type has

ID -------- descendants

ID-> 1   (have no parent)
ID-> 3   (have 1 parent (ID->1))
ID-> 5   (have two parent ((ID->3(ID->1))))

How can I write an optimized sql statement to do this using MySQL?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

山田美奈子 2024-08-27 05:02:37

您也可以实现函数来计算级别
种类：

create function get_level(_id int) returns int
begin
  declare _level int default -1;
  repeat
    set _level = _level + 1;

    select parent_id
    into _id
    from your_table
    where id = _id; 
  until _id is null
  end repeat;
  return _level;  
end

用途：

select id, get_level(id)  
from your_table

我不测试代码。
这种方法并不有效。在大多数情况下，建议在插入/更新时存储和计算级别更好。

Also you could implement function to compute level
Sort of:

create function get_level(_id int) returns int
begin
  declare _level int default -1;
  repeat
    set _level = _level + 1;

    select parent_id
    into _id
    from your_table
    where id = _id; 
  until _id is null
  end repeat;
  return _level;  
end

Usage:

select id, get_level(id)  
from your_table

I do not test a code.
That approach is not effective. In most cases advice to store and compute level on insert/update is better.

回复收藏 0 原文

你げ笑在眉眼 2024-08-27 05:02:37

遗憾的是，MySQL 不支持递归 CTE。但如果父级的数量有限，您可以使用连接来实现：

select p.id
,      case 
           <... more whens here ...>
           when c3.id is not null then 3
           when c2.id is not null then 2
           when c1.id is not null then 1
           else 0
       end as NumberOfChildren
from yourtable p
left join yourtable c1 on c1.parentid = p.id
left join yourtable c2 on c2.parentid = c1.id
left join yourtable c3 on c3.parentid = c2.id
<... more joins here ...>
group by p.id

MySQL unfortunately doesn't support recursive CTE's. But if the number of parents is limited, you can implement this using joins:

select p.id
,      case 
           <... more whens here ...>
           when c3.id is not null then 3
           when c2.id is not null then 2
           when c1.id is not null then 1
           else 0
       end as NumberOfChildren
from yourtable p
left join yourtable c1 on c1.parentid = p.id
left join yourtable c2 on c2.parentid = c1.id
left join yourtable c3 on c3.parentid = c2.id
<... more joins here ...>
group by p.id

回复收藏 0 原文

~没有更多了~