SELECT customer_id, COUNT(order_id) AS total_orders
FROM orders
GROUP BY customer_id;
HAVING过滤
HAVING 函数通常与 GROUP BY 一起使用,用于过滤分组后的结果集
如果过滤条件中使用了聚合函数,在此之后必须使用HAVING来替换WHERE
SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING total_spent > 100;
子查询
子查询是指在一个 SQL 查询中嵌套另一个查询。
子查询可以在 SELECT、INSERT、UPDATE 或 DELETE 语句中使用。
子查询可以用来检索特定条件下的数据,作为另一个查询的条件或结果。
SELECT customer_id,
(SELECT COUNT(*)
FROM orders
WHERE orders.customer_id = customers.customer_id) as order_count
FROM customers
WHERE country = 'USA';