如何在 SQL 中使用 HAVING

一、SQL HAVING 语法

在此 SQL 文章中，您将了解 HAVING 关键字是什么以及如何在SQL语言中实现它。

HAVING 是 SQL 中的一个关键字，它根据特定的单个或多个条件选择由 GROUP BY 关键字过滤的行。

它用于 SQL 是因为 SQL 软件不允许您将 WHERE 子句与它们的聚合函数一起使用。在 SQL 中，我们只能在 SELECT 查询中使用 HAVING 子句。

SQL HAVING 语法：

SELECT First_Column_Name, Second_Column_Name, ........, Nth_Column_Name FROM Table_Name GROUP BY Column_Name HAVING Single or Multiple Conditions;   

在上述语法中，GROUP BY 子句创建一组相同的行，而 HAVING 子句将条件应用于结果组。HAVING 子句对表的列或字段执行其操作。如果我们不能在 HAVING 语法中使用 GROUP BY 子句，则 HAVING 子句的工作方式与 SQL WHERE 子句类似。

二、SQL HAVING 使用步骤

如果您想学习如何在 SQL 表中使用 HAVING 子句，那么您必须按照以下步骤操作：

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

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

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

以下查询创建 Fortis_Hospital 数据库：

CREATE Database Hospital;  

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

CREATE TABLE table_name  
(  
First_column_Name data type (size of the First_column),    
Second_column_Name data type (size of the Second_column),    
Third_column_Name data type (size of the Third_column),    
...    
Nth_column_Name data type (size of the Nth_column)  
);  

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

CREATE TABLE Doctor_Info  
(  
Doctor_ID Int PRIMARY KEY,    
Doctor_Name VARCHAR (100),    
Doctor_Specialist VARCHAR (80),    
Doctor_Salary INT NOT NULL,  
Doctor_Gender Varchar (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);    

以下查询插入在 Fortis_Hospital 工作的医生的记录

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

第 3 步：不使用 HAVING 子句查看表的数据

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

SELECT * FROM Doctor_Info;  

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

Doctor_ID	Doctor_Name	Doctor_Specialist	Doctor_Salary	Doctor_Gender	Doctor_Country
1035	Jones	Malaria_Specialist	25000	Male	United Kingdom
1015	Marry	Diabities_Specialist	30000	Female	United State
1003	Harry	Fever_Specialist	29000	Male	United Kingdom
1044	Ella	Cancer_Specialist	35000	Female	United State
1025	Moria	Corona_Specialist	34000	Other	Europe

第 4 步：使用 HAVING 子句

以下 SQL SELECT 查询使用 HAVING 子句显示表的记录：

SELECT Doctor_Gender, SUM (Doctor_Salary) FROM Table_Name GROUP BY Doctor_Gender HAVING SUM (Doctor_Salary) < 60000;  

输出结果为：

Doctor_Gender	SUM(Doctor_Salary)
Male	54000
Other	34000

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

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

带有 ORDER BY 子句的 HAVING 子句的语法：

SELECT Function_Name (Column_Name) FROM Table_Name GROUP BY Column_Name HAVING Column_Name ORDER BY Column_Name DESC/ASC;

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

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

以下 INSERT INTO SQL语句将记录插入主题表：

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));   
(2224, Physics, Aman, 103),  
(2248, Computer, Bhanu, 101),  
(2208, Hindi, Sonu, 104)  

以下查询显示了 Subject 表的记录：

SELECT * FROM Subject;  

输出结果为：

Subject_ID	Subject_Name	Subject_Teacher	Student_ID
2248	Computer	Bhanu	101
2221	Biology	Punit	103
2201	Chemistry	Suresh	101
2224	Maths	Rohit	103
2248	Computer	Bhanu	105
2208	Hindi	Sonu	104
2221	Biology	Punit	104
2224	Physics	Aman	108
2248	Computer	Bhanu	106
2208	Maths	Sonu	109

下面的 SQL 查询使用带有 HAVING 子句的 SQL ORDER BY 子句：

SELECT Subject_ID, COUNT(Student_ID) >=2 FROM Subject GROUP BY Subject_ID HAVING COUNT(Student_ID) >=2 ORDER BY Subject_ID ASC;  

 输出结果为：

Subject_ID	COUNT(Student_ID) >=2
2208	2
2221	2
2248	3

四、带 MIN 函数的 HAVING 子句

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

带 MIN 函数的 Group BY 子句的语法：

SELECT Column_Name_1, MIN(Column_Name) FROM Table_Name HAVING Column_Name_1; 

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

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

以下 INSERT INTO 语句插入大学生的记录：

INSERT INTO College_Stu_Details VALUES (1001, Anuj, English, 20, 70);  
INSERT INTO College_Stu_Details VALUES (1002, Raman, Maths, 24, 68);  
INSERT INTO College_Stu_Details VALUES (1040, Shyam, Hindi, 19, 92);  
INSERT INTO College_Stu_Details VALUES (1007, Vikash, Computer, 20, 78);  
INSERT INTO College_Stu_Details VALUES (1011, Monu, English, 21, 65);  
INSERT INTO College_Stu_Details VALUES (1014, Jones, Hindi, 18, 93);  
INSERT INTO College_Stu_Details VALUES (1021, Parul, Maths, 20, 97);  
INSERT INTO College_Stu_Details VALUES (1023, Divya, English, 21, 89);  
INSERT INTO College_Stu_Details VALUES (1028, Hemant, Computer, 23, 90);  
INSERT INTO College_Stu_Details VALUES (1030, Nidhi, Hindi, 20, 88);  
INSERT INTO College_Stu_Details VALUES (1032, Priya, English, 22, 99);  
INSERT INTO College_Stu_Details VALUES (1038, Mohit, Maths, 21, 92);  

以下查询只是在屏幕上以表格形式显示学生的记录：

SELECT * FROM College_Stu_Details;  

输出结果为：

Stu_ID	Stu_Name	Stu_Subject	Stu_Age	Stu_Marks
1001	Anuj	English	20	70
1002	Raman	Maths	24	68
1004	Shyam	Hindi	19	92
1007	Vikash	Computer	20	78
1011	Monu	English	21	65
1014	Jones	Hindi	18	93
1021	Parul	Maths	20	97
1023	Divya	English	21	89
1028	Hemant	Computer	23	90
1030	Nidhi	Hindi	20	88
1032	Priya	English	22	99
1038	Mohit	Maths	21	92

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

SELECT Stu_Subject, MIN (Stu_Marks) FROM College_Stu_Details HAVING Stu_Subject;  

输出结果为：

Subject_ID	MIN (Stu_Marks)
English	65
Maths	92
Hindi	88
Computer	78

五、具有 MAX 功能的 HAVING 子句

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

具有 MAX 聚合函数的 Group BY 子句的语法：

SELECT Column_Name_1, MAX(Column_Name) FROM Table_Name HAVING Column_Name_1;  

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

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

以下 INSERT INTO 语句插入大学生的记录：

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

以下查询只是在屏幕上以表格形式显示学生的记录：

SELECT * FROM College_Stu_Details;  

输出结果为：

Stu_ID	Stu_Name	Stu_Subject	Stu_Age	Stu_Marks
101	Anuj	English	20	70
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

以下查询显示了上述 College_Stu_Details 表中每个科目的学生的最高分数：

SELECT Stu_Subject, MAX (Stu_Marks) FROM College_Stu_Details HAVING Stu_Subject;