SQL关联5张含父子关系表，查询大学销售总额方法咨询

问题背景 #

正在开发一个关联5张表的SQL查询，涉及父子关系的层级结构，需求是统计大学的销售总额。现有查询语句无法正确获取目标结果，以下是当前查询语句、相关表结构及数据，以及可行的查询方案。

当前查询语句 #

SELECT invoice.id, invoice.total, categories.type, categories.name 
FROM invoices 
JOIN sales ON sales.invoice_id = invoices.id 
JOIN course ON course.id = sales.course_id 
JOIN category_subject ON category_subject.subject_id = course.subject_id 
JOIN categories ON categories.id = category_subject.category_id

相关表结构及数据 #

categories表（含父子关系） #

invoice表 #

可行查询方案 #

方案1：递归CTE（适配MySQL 8+、PostgreSQL、SQL Server等） #

通过递归CTE获取所有属于大学层级的节点（包括大学本身及其子节点），再关联订单数据统计总额：

WITH RECURSIVE university_categories AS (
    -- 锚点：所有大学节点
    SELECT id, name, parent_id
    FROM categories
    WHERE type = 'university'
    UNION ALL
    -- 递归：获取大学的所有子节点
    SELECT c.id, c.name, c.parent_id
    FROM categories c
    JOIN university_categories uc ON c.parent_id = uc.id
)
SELECT 
    uc.name AS university_name,
    SUM(i.total) AS total_sales
FROM invoices i
JOIN sales s ON s.invoice_id = i.id
JOIN course co ON co.id = s.course_id
JOIN category_subject cs ON cs.subject_id = co.subject_id
JOIN university_categories uc ON uc.id = cs.category_id
GROUP BY uc.name;

方案2：多层JOIN（适配不支持CTE的旧版数据库） #

针对当前数据的层级（大学→院系），直接通过多层JOIN关联院系到大学，再统计总额：

SELECT 
    u.name AS university_name,
    SUM(i.total) AS total_sales
FROM invoices i
JOIN sales s ON s.invoice_id = i.id
JOIN course co ON co.id = s.course_id
JOIN category_subject cs ON cs.subject_id = co.subject_id
JOIN categories d ON d.id = cs.category_id AND d.type = 'department'
JOIN categories u ON u.id = d.parent_id AND u.type = 'university'
GROUP BY u.name;

方案对比 #

• 方案1灵活性强，即使后续层级扩展（如新增专业节点），无需修改查询逻辑即可适配。
• 方案2逻辑更直接，适合当前固定层级的场景，但层级变化时需要调整JOIN层数。

内容的提问来源于stack exchange，提问作者jakline nataly