Sql 简明教程
SQL - Group By Clause
The SQL GROUP BY Clause
SQL GROUP BY 子句与 SELECT 语句结合使用，将相同的数据整理到各组中。此子句在 SELECT 语句中紧跟 WHERE 子句，并先于 ORDER BY 和 HAVING 子句（如果存在的话）。
The SQL GROUP BY clause is used in conjunction with the SELECT statement to arrange identical data into groups. This clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY and HAVING clauses (if they exist).
对基于特定列对表中的记录进行分组的主要目的是针对这些组执行计算。因此，GROUP BY 子句通常与聚合函数（如 SUM()、COUNT()、AVG()、MAX() 或 MIN() 等）一起使用。
The main purpose of grouping the records of a table based on particular columns is to perform calculations on these groups. Therefore, The GROUP BY clause is typically used with aggregate functions such as SUM(), COUNT(), AVG(), MAX(), or MIN() etc.
例如，如果您有一个名为 SALES_DATA 的表，其中包含具有 YEAR、PRODUCT 和 SALES 列的销售数据。为了计算一整年的总销售额，GROUP BY 子句可用于基于年份对该表中的记录进行分组，并使用 SUM() 函数计算每组中的销售总额。
For example, if you have a table named SALES_DATA containing the sales data with the columns YEAR, PRODUCT, and SALES. To calculate the total sales in an year, the GROUP BY clause can be used to group the records in this table based on the year and calculate the sum of sales in each group using the SUM() function.
Syntax
以下是 SQL GROUP BY 子句的基本语法：
Following is the basic syntax of the SQL GROUP BY clause −
SELECT column_name(s)
FROM table_name
GROUP BY column_name(s);其中， column_name(s) 指我们想要对其数据进行分组的表中一个或多个列的名称，而 table_name 指我们想要检索数据的表的名称。
Where, column_name(s) refers to the name of one or more columns in the table that we want to group the data by and the table_name refers to the name of the table that we want to retrieve data from.
GROUP BY Clause with Aggregate Functions
通常，我们会对表的记录进行分组以便对它们执行计算。因此，SQL GROUP BY 子句通常与聚合函数（如 SUM()、AVG()、MIN()、MAX()、COUNT() 等）一起使用。
Typically, we group the record of a table to perform calculations on them. Therefore, the SQL GROUP BY clause is often used with the aggregate functions such as SUM(), AVG(), MIN(), MAX(), COUNT(), etc.
Example
假设我们创建了一个名为 CUSTOMERS 的表，其中包含客户的个人详细信息，包括他们的姓名、年龄、地址和薪水，请使用以下查询：
Assume we have created a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary, using the following 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 语句向该表中插入值，如下所示：
Now insert values into this table using the INSERT statement as follows −
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 created is as shown below −
ID |
NAME |
AGE |
ADDRESS |
SALARY |
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 |
以下 SQL 查询基于 AGE 对 CUSTOMERS 表进行分组，并统计每组中的记录数：
The following SQL query groups the CUSTOMERS table based on AGE and counts the number of records in each group −
SELECT AGE, COUNT(Name) FROM CUSTOMERS GROUP BY AGE;Example
在以下查询中，我们查找每个年龄的最高工资：
In the following query, we are finding the highest salary for each age −
SELECT AGE, MAX(salary) AS MAX_SALARY
FROM CUSTOMERS GROUP BY AGE;Output
以下是以上查询的输出：
Following is the output of the above query −
AGE |
MAX_SALARY |
32 |
2000.00 |
25 |
6500.00 |
23 |
2000.00 |
27 |
8500.00 |
22 |
4500.00 |
24 |
10000.00 |
类似地，我们可以基于 AGE 列对 CUSTOMERS 表的记录进行分组，并使用 MIN()、AVG() 和 SUM() 函数分别计算每组中 SALARY 值的最大值、平均值和总和。
Similarly we can group the records of the CUSTOMERS table based on the AGE column and calculate the maximum salary, average and sum of the SALARY values in each group using the MIN(), AVG() and SUM() functions respectively.
GROUP BY Clause on Single Columns
当我们将 GROUP BY 子句与单个列一起使用时，表中所有在特定列中具有相同值的列都将合并为单个记录。
When we use the GROUP BY clause with a single column, all the rows in the table that have the same value in that particular column will be merged into a single record.
Example
在以下示例中，我们按照 ADDRESS 列对上述创建的 CUSTOMERS 表进行分组，并计算每个城市的客户的平均工资：
In the following example we are grouping the above created CUSTOMERS table by the ADDRESS column and calculating the average salary of the customer from each city −
SELECT ADDRESS, AVG(SALARY) as AVG_SALARY
FROM CUSTOMERS GROUP BY ADDRESS;GROUP BY Clause with Multiple Columns
当我们将 GROUP BY 子句与多列一起使用时，表中在所有指定列中具有相同值的所有行都将合并到单个组中。
When we use the GROUP BY clause with multiple columns, all the rows in the table that have the same values in all of the specified columns will be merged into a single group.
GROUP BY with ORDER BY Clause
在 SQL 中，我们可以将 ORDER BY 子句与 GROUP BY 结合使用，按一列或多列对分组数据进行排序。
We can use the ORDER BY clause with GROUP BY in SQL to sort the grouped data by one or more columns.
Syntax
以下是 SQL 中 ORDER BY 子句与 GROUP BY 子句一起使用的语法：
Following is the syntax for using ORDER BY clause with GROUP BY clause in SQL −
SELECT column1, column2, ..., aggregate_function(columnX) AS alias
FROM table
GROUP BY column1, column2, ...
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;GROUP BY with HAVING Clause
我们还可以将 GROUP BY 子句与 HAVING 子句结合使用，基于特定条件过滤表中分组的数据。
We can also use the GROUP BY clause with the HAVING clause filter the grouped data in a table based on specific criteria.
Syntax
以下是 SQL 中 ORDER BY 子句与 HAVING 子句一起使用的语法：
Following is the syntax for using ORDER BY clause with HAVING clause in SQL −
SELECT column1, column2, aggregate_function(column)
FROM table_name
GROUP BY column1, column2
HAVING condition;Example
在以下查询中，我们按客户的年龄对客户进行分组，并计算每个组的最低工资。使用 HAVING 子句，我们过滤出年龄大于 24 岁组：
In the following query, we are grouping the customers by their age and calculating the minimum salary for each group. Using the HAVING clause we are filtering the groups where the age is greater than 24 −
SELECT ADDRESS, AGE, MIN(SALARY) AS MIN_SUM
FROM CUSTOMERS
GROUP BY ADDRESS, AGE HAVING AGE>24;