Mysql is compatible with the SQL statement Groupby, mysqlgroupby

Mysql is compatible with the SQL statement Groupby, mysqlgroupby

First, create the database hncu and create the stud table.

Add data:

Create table stud (sno varchar (30) not null primary key, sname varchar (30) not null, age int, saddress varchar (30 )); insert into stud VALUES ('123', 'Tom ', 22, 'hunan Yiyang'); insert into stud VALUES ('123', 'jack', 23, 'yiyang '); insert into stud VALUES ('20140901', 'lili', 22, 'yiyang'); insert into stud VALUES ('20140901', 'wang 5', 24, 'beijing' in China); insert into stud VALUES ('123456', 'zhang san', 22, 'yiyang '); insert into stud VALUES ('123456', 'zhang si ', 23, 'yiyang '); insert into stud VALUES ('123456', 'lily', 22, 'hunan Yiyang'); insert into stud VALUES ('1234568 ', 'Liu bei', 24, 'beijing ');

The execution statement is as follows:

<Strong "/kf/ware/vc/" target = "_ blank" class = "keylink"> vcD4NCjxwcmUgY2xhc3M9 "brush: SQL;"> SELECT * FROM stud GROUP BY saddress;

The following error is displayed:

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'hncu.stud.sno' which is not functionally dependenton columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Execute the following sentence:

SELECT saddress as average age FROM stud group by saddress;

-No problem

Then we use MySQL, and then execute the previous Incorrect code:

That is:

SELECT * FROM stud GROUP BY saddress;

The result is as follows:

But you didn't find that the data in the preceding smo, sname, and age columns is incorrect, mySQL forcibly displays the lines with different saddresses found for the first time !!! In fact, this result is incorrect, but MySQL should be compatible with this error!

DOS strictly follows the SQL syntax.

The SQL grop by syntax is: select the columns in the group + the aggregate function from table name group by Column

In terms of syntax format, there are groups first, and then the retrieved columns are determined. The retrieved columns can only be selected in the columns of the group.

Therefore, in the problem, the, B, and c after group by are first determined. The values a, B, and c after select can be changed. That is

The following statements are correct:

select a,b,c from table_name group by a,b,c,d;select a,b from table_name group by a,b,c;select a,max(a) from table_name group by a,b,c;

The following statement is incorrect:

select a,b,c from table_name group by a,b;select a,b,c from table_name group by a;

Because MySQL is powerful, it is compatible with this error !!!

However, DOS cannot. Therefore, an error is reported in DOS, which can be found in MySQL (in fact, the query result is incorrect ).

The preceding section describes how the SQL statement Groupby is compatible with MySQL by mistake. I hope it will help you. If you have any questions, please leave a message, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!