面试题：SQLite复合查询与子查询结合实现复杂统计

Question

设有 'orders' 表，字段有 'order_id', 'customer_id', 'order_date', 'order_amount'；'customers' 表，字段有 'customer_id', 'customer_name'。要求通过复合查询和子查询，找出每个客户的订单总金额大于该客户所在地区平均订单总金额的客户姓名及其订单总金额。假设 'customers' 表中有 'region' 字段表示客户所在地区。

Answer 1

SELECT 
    c.customer_name,
    SUM(o.order_amount) AS total_order_amount
FROM 
    customers c
JOIN 
    orders o ON c.customer_id = o.customer_id
GROUP BY 
    c.customer_name, c.region
HAVING 
    SUM(o.order_amount) > (
        SELECT 
            AVG(sub.total_order_amount)
        FROM 
            (
                SELECT 
                    c1.customer_id, 
                    SUM(o1.order_amount) AS total_order_amount
                FROM 
                    customers c1
                JOIN 
                    orders o1 ON c1.customer_id = o1.customer_id
                GROUP BY 
                    c1.customer_id
            ) sub
        WHERE 
            sub.customer_id IN (
                SELECT 
                    c2.customer_id
                FROM 
                    customers c2
                WHERE 
                    c2.region = c.region
            )
    );