超市会员管理系统的MYSQL复杂的嵌套查询语句

以下是一个超市会员管理系统的MYSQL复杂的嵌套查询语句示例：

SELECT 
    m.member_id, 
    m.member_name, 
    SUM(p.price * oi.quantity) AS total_spent 
FROM 
    members m 
    INNER JOIN orders o ON m.member_id = o.member_id 
    INNER JOIN order_items oi ON o.order_id = oi.order_id 
    INNER JOIN products p ON oi.product_id = p.product_id 
WHERE 
    o.order_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 YEAR) AND NOW() 
    AND m.is_active = 1 
GROUP BY 
    m.member_id 
HAVING 
    total_spent > (SELECT AVG(total_spent) FROM (
        SELECT 
            m.member_id, 
            SUM(p.price * oi.quantity) AS total_spent 
        FROM 
            members m 
            INNER JOIN orders o ON m.member_id = o.member_id 
            INNER JOIN order_items oi ON o.order_id = oi.order_id 
            INNER JOIN products p ON oi.product_id = p.product_id 
        WHERE 
            o.order_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 YEAR) AND NOW() 
            AND m.is_active = 1 
        GROUP BY 
            m.member_id
    ) AS t)
ORDER BY 
    total_spent DESC;

这个查询语句执行以下操作：

- 选择会员ID、会员名称和总消费金额。
- 连接会员表、订单表、订单项表和产品表。
- 仅选择过去一年内活跃的会员。
- 按会员ID分组，并计算每个会员的总消费金额。
- 仅返回消费金额高于平均值的会员。
- 按总消费金额降序排序。