First create the database Hncu, and build the stud table.
Add Data:
Create table Stud (sno varchar() not null Primary key, sname varchar(+) not null, age int, saddress varchar ()); INSERT into stud VALUES(' 1001 ',' Tom ','Yiyang, Hunan ' ); INSERT into stud VALUES(' 1002 ',' Jack ', ' Yiyang '); INSERT into stud VALUES(' 1003 ',' Li Bai ', ' Yiyang '); INSERT into stud VALUES(' 1004 ',' Harry ','Beijing, China ') ; INSERT into stud VALUES(' 1005 ',' Zhang San ',' Yiyang '); INSERT into stud VALUES(' 1006 ',' Zhang Si ', ' Yiyang '); INSERT into stud VALUES(' 1007 ',' John Doe ',$,' Hunan Yiyang ') ; INSERT into stud VALUES(' 1008 ',' Liu Bei ',' Beijing ');
Execute the following statement:
SELECT * FROM stud GROUP BY saddress;
The
shows the following error:
error 1055 (42000 ): Expression #1 of Span class= "Hljs-keyword" >select list is not in group by clause and contains nonaggregated column Hncu.stud.sno ' which is not Functionally dependent on columns in group by clause; This is incompatible with Sql_mode=only_full_ Group_by
Then execute this sentence:
SELECT saddress as 平均年龄 FROM stud GROUP BY saddress;
-No problem
Then we use MySQL, and then execute the previous error code:
That is
SELECT * FROM stud GROUP BY saddress;
We look at the results:
Smoothly passed, but, you find no, the front of the smo,sname,age, the data of the 3 columns is not right ah, yes, MySQL forced to show the first time to find the saddress different lines!!! In fact, this result is not correct, but MySQL should be compatible with this error!
DOS is strictly in accordance with the syntax of SQL.
The grop by syntax for SQL is,
Select Select Column in Group + aggregate function from table name GROUP by group column
From the syntax format, there is a grouping, then the retrieved columns, and the retrieved columns can only be selected in the column that participates in the grouping.
So in the question, the group by A,b,c is determined first. The a,b,c after select is the only thing that can change. That
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;
And because of the strong MySQL, it is compatible with this error!!!
But in DOS is not possible. So there is a DOS error, and in MySQL can find the situation (in fact, the results of this search is not correct).
Sql-group by statement An error in MySQL using a compatible condition