多个CTE的复杂查询示例

这节课我们要玩点真正的查询魔法！我们会写几个用多个CTE的例子，展示它们怎么互相结合，构建复杂又多步骤的查询。这些例子在实际工作中超有用，特别是遇到复杂分析任务的时候。

例子1：学生成绩分析

假设我们有一个大学的数据库，里面有三张表：

表 students：

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    student_name TEXT NOT NULL
);

表 grades：

CREATE TABLE grades (
    grade_id SERIAL PRIMARY KEY,
    student_id INT REFERENCES students(student_id),
    course_id INT NOT NULL,
    grade NUMERIC(3, 1) NOT NULL
);

表 courses：

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_name TEXT NOT NULL
);

任务：拿到那些平均分大于85分的学生名单，显示他们的平均分和他们正在上的课程名。

查询：

WITH high_achievers AS (
    -- 找到平均分高的学生
    SELECT 
        student_id, 
        AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 85
),
student_courses AS (
    -- 找到每个学生选了哪些课程
    SELECT 
        s.student_id, 
        c.course_name
    FROM grades g
    JOIN courses c ON g.course_id = c.course_id
    JOIN students s ON g.student_id = s.student_id
)
-- 合并结果
SELECT 
    s.student_name, 
    ha.avg_grade, 
    sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id
JOIN students s ON s.student_id = ha.student_id;

解释：

1. 第一个CTE（high_achievers）算出每个学生的平均分，只选出分数大于85的。
2. 第二个CTE（student_courses）把学生和他们的课程对应起来。
3. 主查询把两个CTE的数据合起来，得到学生名单、他们的平均分和他们选的课程。

例子2：网店销售报表

假设我们在做一个网店，有这些表：

表 orders（订单）：

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL
);

表 customers（客户）：

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name TEXT NOT NULL
);

表 order_items（订单里的商品）：

CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(order_id),
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);

任务：做一个报表，显示每个客户：

• 总订单数。
• 最近一个月所有订单的总金额。
• 他买过的所有不同商品的列表。

查询：

WITH recent_orders AS (
    -- 选出最近一个月的订单
    SELECT 
        order_id, 
        customer_id, 
        total_amount 
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '1 month'
),
customer_summary AS (
    -- 统计每个客户的订单数和总金额
    SELECT 
        ro.customer_id,
        COUNT(ro.order_id) AS total_orders,
        SUM(ro.total_amount) AS total_spent
    FROM recent_orders ro
    GROUP BY ro.customer_id
),
customer_products AS (
    -- 选出每个客户买过的不同商品
    SELECT DISTINCT
        ro.customer_id,
        oi.product_id
    FROM recent_orders ro
    JOIN order_items oi ON ro.order_id = oi.order_id
)
-- 合并结果
SELECT 
    c.customer_name,
    cs.total_orders,
    cs.total_spent,
    ARRAY_AGG(cp.product_id) AS purchased_products
FROM customer_summary cs
JOIN customers c ON cs.customer_id = c.customer_id
JOIN customer_products cp ON cp.customer_id = c.customer_id
GROUP BY c.customer_name, cs.total_orders, cs.total_spent;

解释：

1. 第一个CTE（recent_orders）选出最近一个月的订单。
2. 第二个CTE（customer_summary）算出每个客户的订单数和总金额。
3. 第三个CTE（customer_products）拿到每个客户买过的不同商品。
4. 最后的查询把这些数据合起来，用ARRAY_AGG()把商品ID做成一个列表。

例子3：员工层级分析

我们有一张员工表：

表 employees：

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name TEXT NOT NULL,
    manager_id INT NULL
);

任务：构建员工的层级结构，从CEO开始。要显示每个人在层级里的level。

查询：

WITH RECURSIVE employee_hierarchy AS (
    -- 从没有manager的员工开始（CEO）
    SELECT 
        employee_id, 
        employee_name, 
        manager_id, 
        1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    -- 加入当前level的下属
    SELECT 
        e.employee_id,
        e.employee_name,
        e.manager_id,
        eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
-- 输出层级结构
SELECT 
    employee_id, 
    employee_name, 
    manager_id, 
    level
FROM employee_hierarchy
ORDER BY level, employee_id;

1. 递归查询从CEO（没有manager的员工）开始。
2. 每一步把当前level的下属加进来，level加1。
3. 主查询选出所有层级，按level和employee_id排序。

实用建议和常见坑

• CTE用太多：能用子查询就别用CTE，CTE有时候会因为数据物化导致性能下降。
• CTE命名：给你的CTE起个简单明了的名字，这样查询更好读。
• 执行顺序：CTE是按声明顺序严格执行的，别忘了。
• 数据分组：只有真的需要的时候才用GROUP BY，不然会多做无用功。

这些例子都说明了CTE怎么帮我们把复杂任务拆成小步骤，让查询更好维护、更易读。现在你已经有工具去用PostgreSQL搞定各种复杂分析任务啦！