Shulou(Shulou.com)06/01 Report--

Let me tell you what group by is today. The content of the article Xiaobian think good, now to share with you, think there is a need for friends can understand, hope to help everyone, the following with the idea of Xiaobian to read together.

Originally today is to continue to sort out the optimization of the log system, but their understanding of some nouns lack, think about it or want to discuss the next group by syntax problem, this problem I have illustrated before, the mind is consistent that: in the SELECT list all columns not included in the group function should be included in the GROUP BY clause.

Verification:

-- ------------------------------ Table structure for mytbl2-- ----------------------------DROP TABLE IF EXISTS `mytbl2`;CREATE TABLE `mytbl2` ( `id` int(11) NULL DEFAULT NULL, `NAME` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `age` int(11) NULL DEFAULT NULL, `dept` int(11) NULL DEFAULT NULL) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ------------------------------ Records of mytbl2-- ----------------------------INSERT INTO `mytbl2` VALUES (1, 'zhangsan', 33, 101);INSERT INTO `mytbl2` VALUES (2, 'li4', 34, 101);INSERT INTO `mytbl2` VALUES (3, 'w5', 34, 102);INSERT INTO `mytbl2` VALUES (4, 'zhao6', 34, 102);INSERT INTO `mytbl2` VALUES (5, 't7', 36, 102);

Purpose: Query the oldest employee department in the table, name

select `NAME`,dept,MAX(age)from mytbl2 GROUP BY dept;

This sql looks semantically correct, but the logic is wrong, and the result is inconsistent with expectations.

Analysis: Find out the maximum age in the table first, as a temporary table, and then cross-check

SELECT *FROM mytbl2 m INNER JOIN (select dept,MAX(age) as maxagefrom mytbl2 GROUP BY dept)ab on ab.dept=m.dept and m.age=ab.maxage;

Of course, this table has single-label autocorrelation because of special tables.

1. Why can't grammar be wrong?

In older versions of sql, this syntax is error-free

2. But what exactly is group by syntax?

Inquiry official website:

https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html

At first I thought about whether it was a version problem, but in fact 5.7 and 8.0 are for this item without any difference

It is also possible to have more than one nonaggregate column in the SELECT list when employing ONLY_FULL_GROUP_BY. In this case, every such column must be limited to a single value in the WHERE clause, and all such limiting conditions must be joined by logical AND, as shown here:

In the case of changing sql_mode, each such column must be restricted to a single value in the WHERE clause, and all such restrictions must be connected by logical and.

Normal sql statements are still consistent with the fields after select and group by, or followed by function operations. Official website knowledge mentioned sql version high syntax will not report error, in the case of changing sql_mode, this syntax error, official website first sentence

SQL-92 and earlier does not permit queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are not named in the GROUP BYclause. For example, this query is illegal in standard SQL-92 because the nonaggregated name column in the select list does not appear in the GROUP BY:

Examples:

SELECT o.custid, c.name, MAX(o.payment) FROM orders AS o, customers AS c WHERE o.custid = c.custid GROUP BY o.custid;

group by is cutid, but the select field has a name in addition to the function operation, this query is illegal, but only emphasizes SQL-92 version, I thought it was a version problem, but SQL8.0 is exactly the same as 5.7, and I have not found a higher version to solve this problem.

Speaking of sql_mode, my previous example mentioned that changing sql_mode and then executing syntax is an error.

Set sql mode set sql_mode='ONLY_FULL_GROUP_BY';

However, this does not prove the problem because sql_mode has been changed.

The group also discussed

Until now, I still can't understand this syntax problem. I can only say that there will be inaccurate data results in special scenarios. The Internet says that mysql has been optimized, and fields without group by will return one randomly. If there is no semantic error, it can only be a logical error, so the example I gave at the beginning is not correct?

The above is the whole content of what group by is. For more information about what group by is, you can search for previous articles or browse the following articles to learn Ha! I believe that Xiaobian will add more knowledge to everyone, I hope you can support it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.