在mysql 8中有一个（with）函数，它递归运行，您可以获得catid的级别
结构->（id cat parent \u id）->（1,24，null），（25915,1），（37569,2），（4,3,1）

WITH recursive parent(id,parent_id,Levels) AS
(
    select id,parent_id, 0 as Levels from table where id =1 //condition
    union all
    select c.id,c.parent_id,(Levels+1) as Levels from table as c  inner join parent as p1 on p1.id = c.parent_id
)
SELECT *
FROM  parent

唯一的限制是，这是在我的sql版本8.x休息的代码将运行良好的任何问题，你可以问这个方法叫cet希望它有帮助

对于每个记录，左键联接子项，然后计算每个级别的唯一值并将它们相加。

select n.record_id
count(distinct n.record_id)+count(distinct n2.record_id)+count(distinct 
n3.record_id)+count(distinct n4.record_id) listingcount
from all_categories n
left join all_categories n2 on n.record_id=n2.parent_category_id
left join all_categories n3 on n2.record_id=n3.parent_category_id
left join all_categories n4 on n3.record_id=n4.parent_category_id
group by n.record_id

你有一个神秘的数据结构，但问题似乎没有那么难。这似乎是你想要的：

select c2.record_id, count(*)
from listings l join
         all_categories c
         on l.cat_id = c.record_id join
         all_categories c2
         on c2.record_id in (c.record_id, c.parent_category_id, c.parent_id)
group by c2.record_id, l.cat_id
order by l.cat_id, c2.record_id;

这个想法很简单。table all_categories 具有完整的层次结构。基本上，您需要将层次结构的所有级别移动到单独的行中，以便可以聚合它们。
这就是 join 至 c2 做。剩下的只是聚合。

假设树的最大深度为四级，则可以使用多个左连接来检索完整树或子树。但这并不是超高效的。考虑以下查询：

SET @subtree_id = 1;

SELECT
    c0.category_id AS c0_id, c0.name AS c0_name,
    c1.category_id AS c1_id, c1.name AS c1_name,
    c2.category_id AS c2_id, c2.name AS c2_name,
    c3.category_id AS c3_id, c3.name AS c3_name,
    l.listing_id
FROM category AS c0
LEFT JOIN category AS c1 ON c1.parent_id = c0.category_id
LEFT JOIN category AS c2 ON c2.parent_id = c1.category_id
LEFT JOIN category AS c3 ON c3.parent_id = c2.category_id
LEFT JOIN listing AS l ON l.category_id = c0.category_id
                       OR l.category_id = c1.category_id
                       OR l.category_id = c2.category_id
                       OR l.category_id = c3.category_id
WHERE c0.category_id = @subtree_id;

它将产生如下结果：

| c0_id | c0_name     | c1_id | c1_name     | c2_id | c2_name   | c3_id | c3_name    | listing_id |
|-------|-------------|-------|-------------|-------|-----------|-------|------------|------------|
| 1     | Real Estate | 2     | Residential | 3     | House     | NULL  | NULL       | NULL       |
| 1     | Real Estate | 2     | Residential | 4     | Apartment | NULL  | NULL       | 1          |
| 1     | Real Estate | 2     | Residential | 4     | Apartment | NULL  | NULL       | 2          |
| 1     | Real Estate | 2     | Residential | 4     | Apartment | NULL  | NULL       | 3          |
| 1     | Real Estate | 2     | Residential | 5     | Condo     | NULL  | NULL       | NULL       |
| 1     | Real Estate | 6     | Commercial  | 7     | Office    | NULL  | NULL       | 4          |
| 1     | Real Estate | 6     | Commercial  | 8     | Retail    | NULL  | NULL       | 5          |
| 1     | Real Estate | 6     | Commercial  | 9     | Other     | 10    | Industrial | 6          |

不幸的是，它只包含完整路径。要匹配预期结果，只需将每行分成4行：

SET @subtree_id = 1;

SELECT
    CASE WHEN level >= 0 THEN c0_id END AS c0_id, CASE WHEN level >= 0 THEN c0_name END AS c0_name,
    CASE WHEN level >= 1 THEN c1_id END AS c1_id, CASE WHEN level >= 1 THEN c1_name END AS c1_name,
    CASE WHEN level >= 2 THEN c2_id END AS c2_id, CASE WHEN level >= 2 THEN c2_name END AS c2_name,
    CASE WHEN level >= 3 THEN c3_id END AS c3_id, CASE WHEN level >= 3 THEN c3_name END AS c3_name,
    COUNT(listing_id) AS lc
FROM (
    SELECT
        c0.category_id AS c0_id, c0.name AS c0_name,
        c1.category_id AS c1_id, c1.name AS c1_name,
        c2.category_id AS c2_id, c2.name AS c2_name,
        c3.category_id AS c3_id, c3.name AS c3_name,
        l.listing_id
    FROM category AS c0
    LEFT JOIN category AS c1 ON c1.parent_id = c0.category_id
    LEFT JOIN category AS c2 ON c2.parent_id = c1.category_id
    LEFT JOIN category AS c3 ON c3.parent_id = c2.category_id
    LEFT JOIN listing AS l ON l.category_id = c0.category_id
                           OR l.category_id = c1.category_id
                           OR l.category_id = c2.category_id
                           OR l.category_id = c3.category_id
    WHERE c0.category_id = @subtree_id
) AS paths
INNER JOIN (
    SELECT 0 AS level UNION ALL
    SELECT 1 UNION ALL
    SELECT 2 UNION ALL
    SELECT 3
) AS levels ON level = 0 AND c0_id IS NOT NULL
            OR level = 1 AND c1_id IS NOT NULL
            OR level = 2 AND c2_id IS NOT NULL
            OR level = 3 AND c3_id IS NOT NULL
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8
ORDER BY 2, 1, 4, 3, 6, 5, 8, 7

在db上演示

子级的0级父级\u id保存在“all \u categories”表中。
所以一个孩子可以通过他们共同的父母id和其他孩子联系起来。
然后同时包含级别0。
困难在于给定的子id不在“listings”表中。
所以要检索列表记录的id，它必须经过类别。
rextester的测试可以在这里找到

SELECT 
cat.record_id as catId, 
pl.listId AS ListingCount,
cat.level
FROM 
(
    SELECT 
     cat1.parent_id, 
     MAX(list.record_id) AS listId
    FROM all_categories AS cat1
    JOIN all_categories AS cat2 ON cat2.parent_id = cat1.parent_id
    JOIN listings list ON list.cat_id = cat2.record_id
    WHERE cat1.record_id = 5915
    GROUP BY cat1.parent_id
) AS pl
LEFT JOIN all_categories AS cat ON (cat.parent_id = pl.parent_id OR cat.record_id = pl.parent_id)
ORDER BY cat.record_id, cat.level;

结果：

catId   ListingCount    level
24      1               0
5915    1               1
7569    1               2

“level”也包含在查询中，因为它可以用来生成html中的类别树。
请注意，如果“listings”表只包含级别0的catu id，那么查询可以简化很多

我相信这适合于递归sql。

CREATE FUNCTION f_total_listings(@root_id INT) RETURNS INT AS
BEGIN
    DECLARE @listing_count INT;
    WITH cat (record_id, parent_category_id) AS
    (
        SELECT root.record_id, root.parent_category_id
        FROM all_categories AS root
        WHERE root.parent_category_id = @root_id
        UNION ALL
        SELECT child.record_id, child.parent_category_id
        FROM cat AS parent, all_categories AS child
        WHERE parent.record_id = child.parent_category_id
    )
    SELECT @listing_count = count(*)
    FROM listings l
    JOIN cat ON l.cat_id = cat.record_id;
    RETURN @listing_count;
END;

SELECT record_id, f_total_listings(record_id) FROM all_categories