当然可以用单条SQL语句完成这个需求，核心就是用**递归CTE（Common Table Expression）**来遍历你的主题层级结构，把每个子主题关联到它所属的根主主题，之后再和DocumentTopics关联统计总数就行。

完整SQL语句 #

WITH RecursiveTopics AS (
    -- 锚点成员：先取出所有主主题（parentTopic为null的）
    SELECT 
        di_name AS root_topic,
        di_name AS current_topic
    FROM Topics
    WHERE parentTopic IS NULL
    
    UNION ALL
    
    -- 递归成员：遍历所有子主题，关联到它们的根主题
    SELECT 
        rt.root_topic,
        t.di_name AS current_topic
    FROM Topics t
    INNER JOIN RecursiveTopics rt ON t.parentTopic = rt.current_topic
)

-- 统计每个主主题及其所有子主题的文档总数
SELECT 
    rt.root_topic,
    COUNT(dt.doc_name) AS total_documents
FROM RecursiveTopics rt
LEFT JOIN DocumentTopics dt ON rt.current_topic = dt.topic_di_name
GROUP BY rt.root_topic
ORDER BY total_documents DESC;

代码解释 #

1. 递归CTE部分（RecursiveTopics）

   • 锚点成员：先筛选出所有的主主题（parentTopic IS NULL），此时root_topic和current_topic都是主主题自己。
   • 递归成员：不断将子主题（t.parentTopic等于上一层的current_topic）关联到对应的根主题，这样不管子主题有多少层级，最终都会被归属到它最顶层的主主题下。

2. 统计部分

   • 用LEFT JOIN关联RecursiveTopics和DocumentTopics，这样即使某个主主题及其子主题都没有关联文档，也会显示总数为0（如果用INNER JOIN会过滤掉这类主主题）。
   • 按root_topic分组，统计关联的文档数量，得到每个主主题的总文档数。

补充说明 #

• 如果你的数据库支持递归CTE（比如MySQL 8.0+、PostgreSQL、SQL Server、Oracle 11gR2+等），这个语句直接就能运行。
• 如果需要同时显示主主题的名称和统计数，直接用root_topic就行，它就是主主题的di_name。

内容的提问来源于stack exchange，提问作者Johny D Good