举一个复杂sql 实例
时间: 2023-06-17 10:06:11 浏览: 50
以下是一个复杂的 SQL 示例,它使用了多个表和聚合函数:
```
SELECT customers.customer_id, customers.customer_name, COUNT(orders.order_id) as total_orders, SUM(order_items.quantity * products.price) as total_spent
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id
INNER JOIN order_items ON orders.order_id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.product_id
WHERE orders.order_date BETWEEN '2020-01-01' AND '2020-12-31'
GROUP BY customers.customer_id, customers.customer_name
HAVING COUNT(orders.order_id) >= 5
ORDER BY total_spent DESC;
```
这个查询将返回所有在2020年内下了至少5个订单的客户,以及他们的总订单数和总消费金额。它使用了四个表(customers,orders,order_items和products)和多个聚合函数(COUNT和SUM),并使用INNER JOIN将它们连接起来。最后,结果按照总消费金额进行降序排序。