如何使用l-tree统计各分类的工单总数

没问题！既然你用的是PostgreSQL的ltree类型来维护分类层级，咱们正好可以利用它的原生层级查询特性来实现你要的统计效果。

先理清楚核心需求：每个分类的统计数要包含自身的工单加上所有子分类的工单总和。你之前的查询只统计了每个分类自己的工单，所以结果和预期不符，咱们来调整一下：

最终查询语句 #

SELECT 
    c.id AS category_id,
    COUNT(t.ticket_id) AS count
FROM 
    categories c
LEFT JOIN 
    categories child ON c.path @> child.path  -- 关联当前分类的所有后代（含自身）
LEFT JOIN 
    tickets t ON child.id = t.category_id
GROUP BY 
    c.id
ORDER BY 
    c.id;

语句解释 #

1. 自连接分类表：categories c LEFT JOIN categories child ON c.path @> child.path
   这里的@>是ltree类型的专属操作符，表示「路径包含」——也就是说，child的路径是c路径的子路径（或完全相同），这样就把每个分类c和它所有的子分类（包括自己）关联起来了。
2. 关联工单表：通过child.id = t.category_id，把所有后代分类对应的工单都关联到当前分类c上。
3. 分组统计：按c.id分组后，用COUNT(t.ticket_id)统计所有关联到的工单数量，就得到了每个分类及其所有子分类的工单总数。

执行这个语句后，得到的结果正好和你期望的一致：

category_id | count
-------------+-------
           1 |     3
           2 |     2
           3 |     1
           4 |     4
           5 |     4
           6 |     1
           9 |     1

对你之前尝试的补充说明 #

• 你用path ~ '*.1.*'来匹配子分类的方式不够准确，比如可能会误匹配到类似10.1.2这样的路径。用c.path @> child.path是ltree层级查询的标准写法，既准确又高效。
• 你之前的LEFT JOIN查询只关联了工单和直接对应的分类，没有包含子分类的工单，所以只能得到每个分类自身的工单数量。

优化建议 #

如果你的categories表数据量很大，建议给path字段创建GIN索引，能大幅提升层级查询的速度：

CREATE INDEX idx_categories_path ON categories USING GIN (path);

内容的提问来源于stack exchange，提问作者Paymahn Moghadasian