在数据库中从子项到最后一个父项的计数的最佳方法是什么？ [英] What whould Be The Best Method To Count From Child To Last Parent Entries In Database

问题描述

在检索父类别时，我需要统计用户在一个或多个子类别中发布的所有帖子。

I need to count all posts posted by users in one or more child categories while retrieving parent category.

就像我有一个表格 categories一样，

Like I have a table 'categories',

ID    Parent_ID
1     0
2     1
3     1
4     2

其他表帖子

P_ID  P_CONTENT  CAT_ID
1     blah blah  2
2     blah blah  4

用户在此处必须有父级的任何子类别下提交帖子。
子类别可能是4个或2个或任何一个。

Users submit there post under any child category which must should have a parent. Child category could maybe 4 or 2 or any.

现在，我的问题是将所有帖子归入该父类别。

And now my problem is to count all posts to there end parent category.

就像在检索父类别时将类别4和2的所有帖子都计入父类别。

Like counting all post of category 4 and 2 into parent category while retrieving parent category.

坦白地说，我没有尝试任何操作，因为在这种情况下我的头脑无法正常工作，我无法做出任何逻辑，而且我也不是查询专家。希望你们能理解。谢谢！

Frankly speaking, I didn't tried anything as my mind is not working in this scenario and i am unable to make any logic and i am not so experts in queries. Hope you guys understand. Thanks!

推荐答案

给出上面的表结构，获取第n个最高父代ID的唯一方法是浏览父母直到没有更多。您无法通过MySQL中的单个查询来执行此操作。如果必须在数据库中执行此操作，则需要使用一个存储到过程来在存在parent_id的情况下继续获取它。

Given your table structure above, the only way to get the n-most parent id is to look through your parents until there are no more. You can't do this with a single query in MySQL. If you have to do it in the database, then you need to use a stored to procedure to keep fetching the parent_id while one exists.

此存储过程将计算给定类别的最高父级。

This stored procedure will calculate the top most parent for a given category.

drop function getlastancestor;
delimiter $$
create function getlastancestor(cat_id int) returns int
deterministic
begin
  declare anc int; -- variable to hold our ancestor
  declare cur int; -- current ancestor we are looking at
  set anc = cat_id; -- initialise it to the category_id we are checking
  set cur = cat_id; -- same again
  while cur > 0 do  -- exit our loop when we find a parent_id = 0
    select ifnull(parent_id, 0) into cur
      from
        (select parent_id
          from categories
          where id = cur) q;    -- find the parent_id of our current ancestor
    if cur > 0 then
      set anc = cur;            -- if it has one, then we update our ancestor to its value
    end if;
  end while;
  return anc;     -- return the top most ancestor
end $$
delimiter ;

，您将使用以下方式：

select getlastancestor(cat_id) parent, count(*) cnt
  from posts
  group by getlastancestor(cat_id);

演示小提琴使用一些编造的数据

这篇关于在数据库中从子项到最后一个父项的计数的最佳方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！