SQL Group By语句

一、SQL Group By语句 介绍

在本 SQL 部分中，您将了解 GROUP BY 关键字是什么以及如何在SQL语言中实现它。我们还将讨论如何将 GROUP BY 子句与 WHERE 子句一起使用。

GROUP BY 是 SELECT 查询中使用的 SQL 关键字，用于使用 SQL 函数排列组中列的相同值。

二、SQL Group By语句 语法

SELECT Column_Name_1, Column_Name_2, ........, Column_Name_N FROM Table_Name GROUP BY Column_Name_1, Column_Name_2, ........, Column_Name_N;  

我们可以在 GROUP BY 子句中使用多个表的字段。我们必须用逗号分隔多个列的名称。

按照下面给出的步骤来学习如何在 SQL 表中使用 GROUP BY 子句：

1. 创建一个简单的数据库和表。
2. 将数据插入表中
3. 查看没有 GROUP BY 子句的插入数据。
4. 使用 GROUP BY 子句。

第 1 步：创建一个简单的数据库和表

首先，您必须在 SQL 中创建一个新数据库。

以下查询创建医院数据库：

CREATE Database Hospital;  

 现在，您必须使用以下 CREATE TABLE 语法创建新表：

CREATE TABLE table_name    
(  
column_Name_1 data type (size of the column_1),    
column_Name_2 data type (size of the column_2),    
column_Name_3 data type (size of the column_3),    
...    
column_Name_N data type (size of the column_1)  
);   

以下查询在医院数据库中创建 Doctor_Info 表：

CREATE TABLE Doctor_Info  
(  
Doctor_ID Int PRIMARY KEY,    
Doctor_Name VARCHAR (100),    
Doctor_Specialist VARCHAR (80),    
Doctor_GenderVarchar (20),  
Doctor_Country Varchar (80)   
) ;  

第 2 步：将数据插入表中

现在，您必须使用以下语法在表中插入数据：

INSERT INTO <Table_Name> (Column_Name_1, Column_Name_2, Column_Name_3, ......., Column_Name_N) VALUES (value_1 of Column_1, value_2, value_3, ...., value_N);

以下SQL插入在医院工作的医生的记录：

INSERT INTO Doctor_Info (Doctor_ID, Doctor_Name, Doctor_Specialist, Doctor_Gender, Doctor_Country) VALUES ( 1035, Jones, Malaria_Specialist, Male, United Kingdom),  
(1015, Marry, Diabties_Specialist, Female, United States),  
(1003, Harry, Fever_Specialist, Male, United Kingdom),  
(1044, Ella, Cancer_Specialist, Female, United States),  
(1025, Moria, Corona_Specialist, Female, Europe);  

第3步：查看不使用GROUP BY的表的插入数据

以下查询以未排序的方式显示 Doctors 的记录：

SELECT * FROM Doctor_Info;  

上述 SELECT 查询的输出如下所示：

Doctor_ID	Doctor_Name	Doctor_Disease	Doctor_Gender	Doctor_Country
1035	Jones	Malaria_Specialist	Male	United Kingdom
1015	Marry	Diabities_Specialist	Female	United State
1003	Harry	Fever_Specialist	Male	United Kingdom
1044	Ella	Cancer_Specialist	Female	United State
1025	Moria	Corona_Specialist	Female	Europe

第 4 步：使用 GROUP BY 子句

以下 SQL 查询使用 GROUP BY 关键字列出每个国家/地区的医生人数：

SELECT COUNT (Doctor_ID), Doctor_Country GROUP BY Doctor_Country;  

上述带有 ODER BY 查询的 SELECT 的输出显示在以下 Doctor 表中：

三、带有 SQL ORDER BY 子句的 GROUP BY

我们还可以在 SQL SELECT 语句中将 ORDER BY 关键字与 GROUP BY 子句一起使用。

SELECT Function_Name (Column_Name) FROM Table_Name GROUP BY Column_Name ORDER BY Function_Name (Column_Name); 

以下查询在 School 数据库中创建新的 Subject 表：

CREATE TABLE Subject  
(  
Subject_ID INT PRIMARY KEY,    
Subject_Name VARCHAR (50),    
Subject_Teacher VARCHAR (70),  
Student_ID INT  
) ;  

以下 INSERT INTO 查询将记录插入Subject表：

INSERT INTO Subject(Subject_ID, Subject_Name, Subject_Teacher, Student_ID) VALUES (2211, Maths, Ramesh, 101),  
(2252, English, Somya, 103),  
(2201, Chemistry, Suresh, 101),  
(2224, Physics, Aman, 103),  
(2248, Computer, Bhanu, 101),  
(2208, Hindi, Sonu, 104),  
(2221, Biology, Punit, 104));  

以下SQL使用带有 GROUP BY 的 SQL ORDER BY 子句：

SELECT Count(Subject_ID), Student_ID FROM Subject GROUP BY Student_ID ORDER BY COUNT(Subject_ID) DESC;  

输出结果为：

四、带有 MIN 函数的 GROUP BY 子句

我们还可以将 MIN 聚合函数与SQL语言中的 GROUP BY 子句一起使用。

SELECT Column_Name_1, MIN(Column_Name) FROM Table_Name GROUP BY Column_Name_1;  

此示例使用下面的 School_Stu_Details 表来理解具有 MIN 聚合函数的 GROUP BY 子句的概念：

CREATE TABLE School_Stu_Details  
(  
Stu_ID INT NOT NULL,   
Stu_Name varchar(100),  
Stu_Subject varchar(50),  
Stu_Age INT,   
Stu_Marks INT  
);   

以下 INSERT INTO 语句插入School_Stu_Details  的记录：

INSERT INTO School_Stu_Details VALUES (101, Anuj, English, 20, 88);  
INSERT INTO School_Stu_Details VALUES (102, Raman, Maths, 24, 98);  
INSERT INTO School_Stu_Details VALUES (104, Shyam, Hindi, 19, 92);  
INSERT INTO School_Stu_Details VALUES (107, Vikash, Computer, 20, 78);  
INSERT INTO School_Stu_Details VALUES (111, Monu, English, 21, 65);  
INSERT INTO School_Stu_Details VALUES (114, Jones, Hindi, 18, 93);  
INSERT INTO School_Stu_Details VALUES (121, Parul, Maths, 20, 97);  
INSERT INTO School_Stu_Details VALUES (123, Divya, English, 21, 89);  
INSERT INTO School_Stu_Details VALUES (128, Hemant, Computer, 23, 90);  
INSERT INTO School_Stu_Details VALUES (130, Nidhi, Hindi, 20, 88);  
INSERT INTO School_Stu_Details VALUES (132, Priya, English, 22, 99);  
INSERT INTO School_Stu_Details VALUES (138, Mohit, Maths, 21, 92);  

以下SQL以表格形式简单显示学生的记录：

SELECT * FROM School_Stu_Details;  

Stu_ID	Stu_Name	Stu_Subject	Stu_Age	Stu_Marks
101	Anuj	English	20	88
102	Raman	Maths	24	98
104	Shyam	Hindi	19	92
107	Vikash	Computer	20	78
111	Monu	English	21	65
114	Jones	Hindi	18	93
121	Parul	Maths	20	97
123	Divya	English	21	89
128	Hemant	Computer	23	90
130	Nidhi	Hindi	20	88
132	Priya	English	22	99
138	Mohit	Maths	21	92

以下SQL显示了上述 School_Stu_Details 表中每个科目的学生的最低分数：

SELECT Stu_Subject, MIN (Stu_Marks) FROM School_Stu_Details GROUP BY Stu_Subject;  

 输出结果为：

Stu_Subject	MIN (Stu Marks)
English	65
Maths	92
Hindi	88
Computer	78

五、带有 MAX 函数的 GROUP BY 子句 

我们还可以将 MAX 聚合函数与结构化查询语言中的 GROUP BY 子句一起使用。

SELECT Column_Name_1, MAX(Column_Name) FROM Table_Name GROUP BY Column_Name_1;  

这个例子使用下面的 School_Stu_Details 表来理解带有 SQL MAX 聚合函数的 GROUP BY 子句的概念：

CREATE TABLE School_Stu_Details  
(  
Stu_ID INT NOT NULL,   
Stu_Name varchar(100),  
Stu_Subject varchar(50),  
Stu_Age INT,   
Stu_Marks INT  
);   

以下 INSERT INTO 语句插入 School_Stu_Details 的记录：

INSERT INTO School_Stu_Details VALUES (101, Anuj, English, 20, 88);  
INSERT INTO School_Stu_Details VALUES (102, Raman, Maths, 24, 98);  
INSERT INTO School_Stu_Details VALUES (104, Shyam, Hindi, 19, 92);  
INSERT INTO School_Stu_Details VALUES (107, Vikash, Computer, 20, 78);  
INSERT INTO School_Stu_Details VALUES (111, Monu, English, 21, 65);  
INSERT INTO School_Stu_Details VALUES (114, Jones, Hindi, 18, 93);  
INSERT INTO School_Stu_Details VALUES (121, Parul, Maths, 20, 97);  
INSERT INTO School_Stu_Details VALUES (123, Divya, English, 21, 89);  
INSERT INTO School_Stu_Details VALUES (128, Hemant, Computer, 23, 90);  
INSERT INTO School_Stu_Details VALUES (130, Nidhi, Hindi, 20, 88);  
INSERT INTO School_Stu_Details VALUES (132, Priya, English, 22, 99);  
INSERT INTO School_Stu_Details VALUES (138, Mohit, Maths, 21, 92);  

以下SQL以表格形式简单显示学生的记录：

SELECT * FROM School_Stu_Details;  

输出结果为：

Stu_ID	Stu_Name	Stu_Subject	Stu_Age	Stu_Marks
101	Anuj	English	20	88
102	Raman	Maths	24	98
104	Shyam	Hindi	19	92
107	Vikash	Computer	20	78
111	Monu	English	21	65
114	Jones	Hindi	18	93
121	Parul	Maths	20	97
123	Divya	English	21	89
128	Hemant	Computer	23	90
130	Nidhi	Hindi	20	88
132	Priya	English	22	99
138	Mohit	Maths	21	92

以下SQL显示了上述 School_Stu_Details 表中每个科目的学生的最高分： 

SELECT Stu_Subject, MAX (Stu_Marks) FROM School_Stu_Details GROUP BY Stu_Subject;  

输出结果为：

Stu_Subject	MAX (Stu Marks)
English	99
Maths	98
Hindi	93
Computer	90