计算机知识分享网

【mysql】mysql中group by语句详解示例

GROUP BY子句在SQL中用于对数据进行分组，通常与聚合函数如COUNT(), SUM(), AVG(), MAX(), MIN()等一起使用。下面我将提供一个基础的GROUP BY语句的例子。
假设我们有一个名为orders的表，其中包含以下列：order_id, customer_id, product_id, quantity, 和 order_date。如果我们想要找出每个客户购买的产品总数，我们可以使用以下SQL查询：
SELECT customer_id, COUNT(product_id) as total_products
FROM orders
GROUP BY customer_id;
如果你还想要过滤出特定日期范围内的订单，你可以添加一个WHERE子句，如下所示：
SELECT customer_id, COUNT(product_id) as total_products
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY customer_id;
如果你想进一步了解每个客户购买的每种产品的数量，你可以将GROUP BY子句扩展到包括product_id
SELECT customer_id, product_id, SUM(quantity) as total_quantity
FROM orders
GROUP BY customer_id, product_id;
测试SQL
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL
);


INSERT INTO orders (customer_id, product_id, quantity)
VALUES (1, 1001, 5),
       (2, 1002, 3),
       (3, 1003, 2),
       (1, 1004, 1),
       (4, 1005, 4),
       (5, 1006, 7),
       (2, 1007, 2),
       (6, 1008, 1),
       (7, 1009, 3),
       (8, 1010, 6);

#Mysql