Having clause description & amp; execution sequence, having clause

Having clause details & execution sequence, having clause

1. The Having clause is used to process the query table structure. It can be used together with group by After grouping and can contain aggregate functions.
2. The Having clause can be used after group by. The syntax is similar to the where clause. The where clause is used before group.
3. The fields in the having clause must be in the upper-level result table.
4. Aggregate functions can be used in having clauses. Aggregate functions cannot be used in where statements.
5. Order of Having clause execution: where Statement> group by> aggregate function> Having
6. Mysql will automatically aggregate the grouped data after group by, and select the first in each group. (The Mysql syntax is not rigorous. Aggregation is required for ungrouped fields in Hive .)

SQL statementsExecution sequence:

select col_name from tablewhere col_name > xxxgroup by col_namehaving ...order by ... desc

We will understand the Having clause through examples as follows

1. Create a data table

/*Navicat MySQL Data TransferSource Server         : arvinSource Server Version : 50624Source Host           : localhost:3306Source Database       : blogTarget Server Type    : MYSQLTarget Server Version : 50624File Encoding         : 65001Date: 2017-11-07 10:02:24*/SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for test-- ----------------------------DROP TABLE IF EXISTS `test`;CREATE TABLE `test` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(255) DEFAULT NULL,  `score` varchar(255) DEFAULT NULL,  `num` varchar(255) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;-- ------------------------------ Records of test-- ----------------------------INSERT INTO `test` VALUES ('1', 'zhangsan', '80', 'Math');INSERT INTO `test` VALUES ('2', 'zhangsan', '80', 'English');INSERT INTO `test` VALUES ('3', 'zhangsan', '90', 'Code');INSERT INTO `test` VALUES ('4', 'lisi', '70', 'Math');INSERT INTO `test` VALUES ('5', 'lisi', '80', 'English');INSERT INTO `test` VALUES ('6', 'lisi', '100', 'Code');INSERT INTO `test` VALUES ('7', 'wangwu', '80', 'Math');INSERT INTO `test` VALUES ('8', 'wangwu', '80', 'English');INSERT INTO `test` VALUES ('9', 'wangwu', '90', 'Code');

Use the having clause without using group

SELECT t.*from test tHAVING t.score>75;

Result:

Conclusion: The having clause is used only to limit the results without the combination of group by. It is equivalent to the where clause.

Having clause understanding

• Question 1: Find out the student (score) whose number of each subject (num) is greater than 75 ).

Having can process the results of the group by statement as follows:

SELECT t.*from test tGROUP BY t.`name`HAVING t.score>75;

Result:

It seems that there is no problem, so let's change the data and change the two data in the figure:

Run again and the result is incorrect,

The correct results should still be zhangsan and lisi. Why?
The reason is simple: refer to the sixth point about the rigor of the group by function in Mysql.
The having clause is used to process the previous result table.And we only run the results before having, that is, the results of group by, we will find the following:

The having clause judges the result. In his opinion, there are only three records, and each record is greater than 75. Therefore, the result of the preceding error is displayed.
Therefore, the correct algorithm is to achieve a minimum score greater than 75.

SELECT `name`,MIN(score) as scorefrom test tGROUP BY t.`name`HAVING score>75;

For example:

Note: The fields in the having clause must be in the result table.

Having clause and Aggregate functions

Question 2: calculate the average score of over 80 students.

SELECT `name`, scorefrom test tGROUP BY t.`name`HAVING AVG(score)>80;

Here we can find that:
The execution sequence of the aggregate function AVG is before Having.

Question 3: calculate an average score greater than 80 and a total score greater than 250.

SELECT `name`, scorefrom test tGROUP BY t.`name`HAVING AVG(score)>80AND SUM(score)>250;

Copyright Disclaimer: This article is an original article by the blogger. You are welcome to repost it. Please indicate the source for reprinting!