可以使用递归存储过程来实现这一点。这样做的好处是，无论祖先（如子女、孙子、曾孙等）有多深，它都能工作。

delimiter //
drop PROCEDURE if EXISTS listing_count //
create procedure listing_count(in parentID int(11), out thesum int(11))
begin 
  declare childID int(11);
  declare childSum int(11);
  declare finished int default 0;
  declare childID_cursor cursor for select id from category where related=parentID;
  declare continue handler for not found set finished = 1;
  select count(*) into thesum from listing_category where category=parentID;
  open childID_cursor;
  get_children: LOOP
    fetch childID_cursor into childID;
    if finished = 1 then 
      LEAVE get_children;
    end if;
    call listing_count(childID, childSum);
    set thesum = thesum + childSum;
  end loop get_children;
  close childID_cursor;
end
//

使用您的数据，此查询将生成预期结果（8）：

SET @@SESSION.max_sp_recursion_depth=25;
call listing_count(1, @x);
select @x;

如果您真的想要一个函数，您可以将过程 Package 在一个函数中（因为mysql不允许您创建递归函数）：

DELIMITER //
drop function if exists lc//
create function lc(id int(11)) RETURNS int(11)
BEGIN
  declare sum int(11);
  call listing_count(id, sum);
  return sum;
END
//
select lc(1)

输出：

8

如果希望所有相关的类别都在一列中，则需要合并l1（仅一行）、l2和l3中的所有id。

SELECT l1.* FROM category l1
WHERE l1.id = ID
UNION ALL
SELECT l2.* FROM category l1
    INNER JOIN category l2 ON l1.related = l2.id
WHERE l1.id = ID
UNION ALL
SELECT l3.* FROM category l1
    INNER JOIN category l2 ON l1.related = l2.id
    INNER JOIN category l3 ON l2.related = l3.id
WHERE l1.id = ID

一旦你有了所有的身份证，你就可以得到计数了：
下面是一个查询，它将为您统计记录：

SELECT COUNT(*) FROM listing_category 
WHERE category IN (
SELECT l1.* FROM category l1
    WHERE l1.id = ID
    UNION ALL
    SELECT l2.* FROM category l1
        INNER JOIN category l2 ON l1.related = l2.id
    WHERE l1.id = ID
    UNION ALL
    SELECT l3.* FROM category l1
        INNER JOIN category l2 ON l1.related = l2.id
        INNER JOIN category l3 ON l2.related = l3.id
    WHERE l1.id = ID)