Shulou(Shulou.com)05/31 Report--

Why the columns in the original table can not be directly referenced after GROUP BY? in view of this question, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Restrictions on SELECT columns after GROUP BY

The standard SQL stipulates that when you make an aggregate query on a table, you can only write the following three kinds of content in the SELECT clause: aggregate key specified by the GROUP BY clause, aggregate function (SUM, AVG, etc.), and constant. Let's look at an example:

We have a student class table (tbl_student_class) and the data are as follows:

DROP TABLE IF EXISTS tbl_student_class; CREATE TABLE tbl_student_class (id int (8) unsigned NOT NULL AUTO_INCREMENT COMMENT 'self-increasing primary key', sno varchar (12) NOT NULL COMMENT 'student number', cno varchar (5) NOT NULL COMMENT 'class number', cname varchar (20) NOT NULL COMMENT 'class name', PRIMARY KEY (id)) COMMENT=' student class table' -Records of tbl_student_class-INSERT INTO tbl_student_class VALUES ('1x, '20190607001, 0607, Class 7) INSERT INTO tbl_student_class VALUES (Class 2, 20190607002, 0607, Class 7); INSERT INTO tbl_student_class VALUES (Class 3, 20190608003, 0608, Class 8); INSERT INTO tbl_student_class VALUES (Class 4, 20190608004, 0608, Class 8); INSERT INTO tbl_student_class VALUES (Class 5, 20190609005, 0609, Class 9) INSERT INTO tbl_student_class VALUES ('615,' 20190609006, '0609, Class 9')

We want to count how many students there are in each class (class number, class name), as well as the largest student number. How should we write this query SQL? I think everyone will.

SELECT cno,cname,count (sno), MAX (sno) FROM tbl_student_class GROUP BY cno,cname

But some people will think, cno and cname are one-on-one, once cno is confirmed, cname is sure, then SQL can be written in this way?

SELECT cno,cname,count (sno), MAX (sno) FROM tbl_student_class GROUP BY cno

The execution reported an error:

[Err] 1055-Expression # 2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.tbl_student_class.cname' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Tip: the second expression in the SELECT list (cname) is not in the clause of GROUP BY, nor is it an aggregate function; this is incompatible with the sql pattern: ONLY_FULL_GROUP_BY.

Why can't columns in the original table (not in the GROUP BY clause) be referenced directly after GROUP BY? Don't worry, let's look down slowly.

SQL mode

The MySQL server can run in different SQL modes and can apply these modes in different ways for different clients, depending on the value of the sql_mode system variable. DBA can set the global SQL mode to match the site server operational requirements, and each application can set its session SQL mode to its own requirements.

The schema affects the SQL syntax supported by MySQL and the data validation checks it performs, which makes it easier to use MySQL in different environments and to use MySQL with other database servers. More details can be found on the official website: Server SQL Modes.

The content will be slightly different (including default values) depending on the MySQL version, so be careful to check it in accordance with your own MySQL version.

SQL schemas are mainly divided into two categories: syntax support classes and data checking classes, commonly used as follows

Syntax support class

ONLY_FULL_GROUP_BY

For GROUP BY aggregation operations, if the columns in the SELECT, HAVING, or ORDER BY clause do not appear in the GROUP BY, then the SQL is illegal

ANSI_QUOTES

When ANSI_QUOTES is enabled, you cannot refer to a string in double quotes because it is interpreted as an identifier and works the same as `. After setting it, update t set F1 = ""... Syntax errors such as Unknown column''in field list will be reported

PIPES_AS_CONCAT

Treat | | as the concatenation operator of the string rather than the OR operator, which is the same as the Oracle database and similar to the string concatenation function CONCAT ().

NO_TABLE_OPTIONS

MySQL-specific syntax parts, such as ENGINE, are not output when using SHOW CREATE TABLE, which needs to be considered when migrating across DB types using mysqldump

NO_AUTO_CREATE_USER

Literally, users are not created automatically. We are used to using GRANT when authorizing MySQL users. ON... TO dbuser is on the way to create users. When this option is set, it is similar to oracle operation, and users must be established before authorization.

Data check class

NO_ZERO_DATE

It is considered that the date '0000-0000' is illegal, which is related to whether the later strict mode is set or not.

1. If strict mode is set, NO_ZERO_DATE is naturally satisfied. But if it is INSERT IGNORE or UPDATE IGNORE,'0000-00-00', it still allows and only shows warning.

2. If NO_ZERO_DATE is set in non-strict mode, the effect is the same as above. '0000-00' allows but displays warning;. If NO_ZERO_DATE,no warning is not set, it will be regarded as a perfectly legal value.

3. The NO_ZERO_IN_DATE situation is similar to the above, except that it controls whether the date and day can be 0, that is, whether 2010-01-00 is legal.

NO_ENGINE_SUBSTITUTION

What to do if the required storage engine is disabled or uncompiled when you specify ENGINE using ALTER TABLE or CREATE TABLE. When NO_ENGINE_SUBSTITUTION is enabled, an error is thrown directly; when this value is not set, CREATE is replaced by the default storage engine, ATLER does not change, and a warning is thrown

STRICT_TRANS_TABLES

Set it to enable strict mode. Note that STRICT_TRANS_TABLES is not a combination of several policies. It refers to how to deal with low or invalid values in INSERT and UPDATE:

1. Passing''to int mentioned earlier is illegal in strict mode, but becomes 0 if non-strict mode is enabled, resulting in a warning.

2. Out Of Range, which becomes the maximum boundary value inserted.

3. When the new row to be inserted does not contain the value of a non-DEFAULT column whose definition does not have an explicit NULL clause, the column is missing a value

Default mode

When we do not modify the configuration file, MySQL has its own default mode; the default mode varies with different versions

-- View MySQL version SELECT VERSION ();-- View sql_mode SELECT @ @ sql_mode

As we can see, the default mode of 5.7.21 contains:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

The first one, ONLY_FULL_GROUP_BY, constrains: when we do an aggregate query, the columns of SELECT cannot directly contain columns in non-GROUP BY clauses. What if we get rid of the model (from "strict mode" to "loose mode")?

We found that the SQL that reported the error above

-- SELECT cno,cname,count (sno), MAX (sno) FROM tbl_student_class GROUP BY cno can be executed in loose mode

It can be executed normally, but in general, this configuration is not recommended, and the online environment is often in "strict mode" rather than "loose mode". Although in the case, whether it is the "strict mode" or "loose mode", the result is correct, that is because cno and cname are the only corresponding, if cno and cname are not the only corresponding, then the value of cname in the "loose mode" is random, which will cause problems that are difficult to troubleshoot, and those who are interested can try. Then why is there an ONLY_FULL_GROUP_BY mode? Let's move on.

Stage

Order is a concept used to distinguish the order of a set or predicate. In predicate logic, predicates are classified according to the order of the input value.

Predicates such as = or BETWEEEN with an input value of one row are called "first-order predicates", while predicates such as EXISTS that enter a set whose value is a row are called "second-order predicates" (the input value of HAVING is also a set, but it is not a predicate). And so on, third-order predicate = input predicate whose value is "set of set", fourth-order predicate = input predicate whose value is "set of set", but there is no more than third-order predicate in SQL, so don't worry too much about it. Reply to interview questions in the official account of Java bosom friend and send you a treasure book of Java interview questions.

To make it simple, the following figure

When it comes to order, we have to talk about set theory; set theory is the foundation of SQL language, because of this feature, SQL is also called set-oriented language. Only by thinking from the perspective of set, can we understand the powerful power of SQL. Through the above picture, I believe everyone can see that there is no more in-depth explanation here, those who are interested can check the relevant information.

Why the columns in the original table can no longer be referenced after aggregation

Many people know the limitations of aggregate queries, but few people correctly understand why there are such constraints. The cname in the table tbl_student_class stores the class information for each student.

But it should be noted that the cname here is only the attribute of each student, not the attribute of the group, and the GROUP BY is an aggregation operation, and the object of the operation is a group composed of multiple students. Therefore, the attributes of the group can only be statistical attributes such as average or sum, as shown in the following figure.

It is OK to ask about the cname of each student, but there is no point in asking about the cname of a group of students. For the group, only "how many students are there" or "what is the maximum student number?" This kind of question is meaningful.

Forcibly applying attributes applicable to individuals to groups is purely a classification error; while the role of GROUP BY is to divide elements into several subsets, after using GROUP BY aggregation, the operands of SQL will be changed from 0-order "rows" to 1-order "row sets", at this time, row attributes can not be used.

The world of SQL is actually a hierarchical society. It is forbidden to apply the attributes of lower-order concepts to higher-order concepts, which will lead to order confusion. At this point, I'm sure you all understand why the columns in the original table can no longer be referenced after aggregation.

A single element set is also a set.

The current set theory holds that a single element set is a normal set. Like empty sets, single-element sets are mainly defined to maintain the integrity of the theory. Therefore, for SQL based on set theory, it is also necessary to strictly distinguish between elements and single-element sets. As a result, there is a very striking hierarchical difference between element an and collection {a}.

A ≠ {a}

The difference between these two levels corresponds to the difference between the WHERE clause and the HAVING clause in SQL. The WHERE clause is used to deal with order 0 objects such as "rows", while the HAVING clause is used to deal with order 1 objects such as "sets".

Summary

1. SQL strictly distinguishes levels, including EXISTS in predicate logic and GROUP BY in set theory.

2. With hierarchy, attributes that apply to individuals do not apply to groups, which is why columns in the original table cannot be directly referenced in the SELECT clause of the aggregate query

3. Generally speaking, the attributes of a single element collection are the same as those of its unique element. This kind of collection which contains only one element makes people feel that it is not necessary to treat it as a set, but in order to maintain the integrity of the theory, we still have to make a strict distinction between elements and single-element sets.

The answer to the question about why GROUP BY can not directly refer to the columns in the original table is shared here. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

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.