mysql_group by 奇怪问题解决

我回去查了一下关于mysql group by 奇怪的现像,

可以通过修改sql_mode的方法改变这种奇怪的现像。

例如下:

mysql>SELECTjob,sal,deptno

->FROMemp

->GROUPbydeptno;

+----------+---------+--------+

|job|sal|deptno|

+----------+---------+--------+

|MANAGER|2450.00|10|

|CLERK|800.00|20|

|SALESMAN|1600.00|30|

+----------+---------+--------+

3rowsinset(0.04sec)

mysql>setsql_mode="STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY";

QueryOK,0rowsaffected(0.00sec)

mysql>SELECTjob,sal,deptno

->FROMemp

->GROUPbydeptno;

ERROR1055(42000):'tao.emp.JOB'isn'tinGROUPBY

mysql>SELECTcount(job),count(sal),deptno

->FROMemp

->GROUPBYdeptno;

+------------+------------+--------+

|count(job)|count(sal)|deptno|

+------------+------------+--------+

|3|3|10|

|5|5|20|

|6|6|30|

+------------+------------+--------+

3rowsinset(0.00sec)

//-------------------------------------------------

//以上修改sql_mode只在当前会话中生效.

//如果需要长期有效请修改my.ini文件中

#sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

sql_mode="STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY"

相关推荐