The problem of group by in Mysql. .

Question

In Mysql, the following writing method is allowed. But is it strict or not? Does it support writing like this? If you have any doubts about how to overcome this problem, please help me.

select * from user group by user_name;

Answer 1

最详细的文档说明在官网找到了。

MySQL 5.7.5 and up implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default. For a description of pre-5.7.5 behavior, see the MySQL 5.6 Reference Manual.)

来源：https://dev.mysql.com/doc/ref...

Answer 2

The fields in select need to be forced to be written out in group by
select user_name from user group by user_name;

http://blog.csdn.net/u2830560...

Answer 3

First of all, understand the concept of grouping and what kind of effects can be achieved after grouping;
Grouping is for statistical analysis based on the attribute of group;
For example, in a student table, grouped by gender, you can count how many boys and how many girls people.
In the query result column, it must be composed of aggregate functions such as sum and count. For example:
select count(*),sex from student group by sex;
If it is select * from user group by user_name; what kind of statistical result do you want to get?
mysql5.6 can use this writing method select * from user group by user_name by default, but the statement is actually converted internally;
mysql5.7 and later cannot use this writing method by default, and an error will be reported.
So before writing, think about what I need to count through grouping and use appropriate aggregation functions

Answer 4

Three points to note when using GROUP BY in SQL statements
1: Aliases cannot be used;
2: Except for function fields, the fields appearing in select must appear in group by,
3: Aliases cannot use reserved words
These three There are no requirements for clicking MYSQL!
Let’s look at your statement again. If the user table has only one field user_name, then this statement has no problem.
If the user table has more than one field, then this statement has no problem in mysql, but there is a problem in oracle and sqlserver.

Answer 5

select * from user group by user_name;
//这么写其实也没问题 但是 实际上 你使用 group by 的时候 你需要用的 就只有 user_name 这个字段吧（通常来说）
//用什么字段就取什么字段就好。不一定要用 ‘*’ 用谁取谁就好

Answer 6

If the user_name field exists, then there is no problem. Group by is grouping by fields. It is usually used together with aggregate functions. SQL like yours can also be executed. However, user_name is usually unique, and it makes no sense to group by unique fields.

Answer 7

This is a special function support for mysql. As @xuexiphpa said, it can be turned off through parameters.

But it is not recommended to use. After group by, the number of records returned is theoretically less than before grouping. Generally, some statistical data will be returned through aggregate functions.
Use select * directly without confirming which record is returned.

Answer 8

Generally, a combination of aggregate functions is more commonly used

Answer 9

After group by aggregation grouping, it is best to keep the elements in the select clause only:
1, constants
2, column names specified by group by
3, aggregate functions, such as count(), avg(), sum( *)Wait

Your result* only lists one record for each group, and I don’t know whether it is the first one or a random value