Mysql 简明教程
MySQL - GROUP BY Clause
MySQL GROUP BY Clause
MySQL 中的 GROUP BY 子句用于将表中的相同数据按组排列。
The GROUP BY clause in MySQL is used to arrange identical data in a table into groups.
例如，假设你有一个包含日期、产品和销售额的组织销售数据的表。若要计算某一特定年份的总销售额，则可使用 GROUP BY 子句对该年份生产的产品的销售额进行分组。类似地，你可以按日期对数据进行分组，以计算每个日期的总销售额，或按产品和日期的组合对数据进行分组，以计算每天每种产品的总销售额。
For example, let us suppose you have a table of sales data of an organization consisting of date, product, and sales amount. To calculate the total sales in a particular year, the GROUP BY clause can be used to group the sales of products made in that year. Similarly, you can group the data by date to calculate the total sales for each day, or by a combination of product and date to calculate the total sales for each product on each day.
此 GROUP BY 子句紧跟 SQL 语句中的 WHERE 子句并位于 ORDER BY 或 HAVING 子句之前（如果它们存在）。你可以使用 GROUP BY 对列中的值进行分组，并且可以在需要时对该列执行计算。你可以在分组的列上使用 COUNT、SUM、AVG 等函数。
This GROUP BY clause follows the WHERE clause in an SQL statement and precedes the ORDER BY or HAVING clause (if they exist). You can use GROUP BY to group values from a column, and, if you wish, perform calculations on that column. You can use COUNT, SUM, AVG, etc., functions on the grouped column.
Syntax
以下是将 GROUP BY 与 SELECT 语句一起使用的基本语法 −
Following is the basic syntax to use GROUP BY with SELECT statement −
SELECT column_name(s) FROM table_name
GROUP BY [condition | column_name(s)];Example
此示例演示如何使用带有 GROUP BY 子句的 aggregate functions 。
This example demonstrates how to use aggregate functions with GROUP BY clause.
首先，使用以下 CREATE TABLE 查询创建名为 CUSTOMERS 的表 −
First of all, create a table named CUSTOMERS, using the following CREATE TABLE query −
CREATE TABLE CUSTOMERS (
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);现在，使用以下 INSERT 语句将以下记录插入到 CUSTOMERS 表中 −
Now, insert the following records into the CUSTOMERS table using the following INSERT statement −
INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);该表创建如下 −
The table is created as follows −
现在，使用以下 GROUP BY 查询根据其年龄对客户进行分组 −
Now, use the following GROUP BY query to group the customers based on their age −
SELECT AGE, COUNT(Name) FROM CUSTOMERS GROUP BY AGE;MySQL GROUP BY on Single Column
当我们对单个列使用 GROUP BY 子句时，该列中的所有公共值将被加在一起，形成一条记录。
When we use the GROUP BY clause on a single column, all common values in that column will be added together making it a single record.