解决 this is incompatible with sql_mode=only_full_group_by Details 问题

2017-01-13 10:47:19来源:oschina作者:黄劼人点击



解决
this is incompatible with sql_mode=only_full_group_by
Details
问题
文档来源 http://docs.oracle.com/cd/E17952_01/mysql-5.7-en/miscellaneous-functions.html#idm139917231730832
解决办法
原始sql,可行
```
SELECT
max(`vema`.`devices_status_history`.`INSERT_TIME`) AS `SCAN_TIME`,
`vema`.`devices_status_history`.`DEVICES_ID`
FROM `vema`.`devices_status_history`
WHERE `vema`.`devices_status_history`.`STATUS_CHANGE` = 11
GROUP BY `vema`.`devices_status_history`.`DEVICES_ID`;
```
出现问题sql: this is incompatible with sql_mode=only_full_group_by Details
```
SELECT
max(`vema`.`devices_status_history`.`INSERT_TIME`) AS `SCAN_TIME`,
`vema`.`devices_status_history`.`DESCRIPTION`,
`vema`.`devices_status_history`.`DEVICES_ID`
FROM `vema`.`devices_status_history`
WHERE `vema`.`devices_status_history`.`STATUS_CHANGE` = 11
GROUP BY `vema`.`devices_status_history`.`DEVICES_ID`;
```
解决问题sql
```
SELECT
max(
`vema`.`devices_status_history`.`INSERT_TIME`)AS `INSERT_TIME`,
`vema`.`devices_status_history`.`DEVICES_ID`,
ANY_VALUE(`vema`.`devices_status_history`.`DESCRIPTION`)
FROM `vema`.`devices_status_history`
WHERE
`vema`.`devices_status_history`.`STATUS_CHANGE`
= 40
GROUP BY
`vema`.`devices_status_history`.`DEVICES_ID`,`vema`.`devices_status_history`.`STATUS_CHANGE`;
```
* ANY_VALUE(arg)
This function is useful for GROUP BY queries when the ONLY_FULL_GROUP_BY SQL mode is enabled, for cases when MySQL rejects a query that you know is valid for reasons that MySQL cannot determine. The function return value and type are the same as the return value and type of its argument, but the function result is not checked for the ONLY_FULL_GROUP_BY SQL mode.
For example, if name is a nonindexed column, the following query fails with ONLY_FULL_GROUP_BY enabled:
mysql> SELECT name, address, MAX(age) FROM t GROUP BY name;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP
BY clause and contains nonaggregated column 'mydb.t.address' which
is not functionally dependent on columns in GROUP BY clause; this
is incompatible with sql_mode=only_full_group_by
The failure occurs because address is a nonaggregated column that is neither named among GROUP BY columns nor functionally dependent on them. As a result, the address value for rows within each name group is nondeterministic. There are multiple ways to cause MySQL to accept the query:
* Alter the table to make name a primary key or a unique NOT NULL column. This enables MySQL to determine that address is functionally dependent on name; that is, address is uniquely determined by name. (This technique is inapplicable if NULL must be permitted as a valid name value.)
* Use ANY_VALUE() to refer to address:
SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;
In this case, MySQL ignores the nondeterminism of address values within each name group and accepts the query. This may be useful if you simply do not care which value of a nonaggregated column is chosen for each group. ANY_VALUE() is not an aggregate function, unlike functions such as SUM() or COUNT(). It simply acts to suppress the test for nondeterminism.
* Disable ONLY_FULL_GROUP_BY. This is equivalent to using ANY_VALUE() with ONLY_FULL_GROUP_BY enabled, as described in the previous item.
ANY_VALUE() is also useful if functional dependence exists between columns but MySQL cannot determine it. The following query is valid because age is functionally dependent on the grouping column age-1, but MySQL cannot tell that and rejects the query with ONLY_FULL_GROUP_BY enabled:
SELECT age FROM t GROUP BY age-1;
To cause MySQL to accept the query, use ANY_VALUE():
SELECT ANY_VALUE(age) FROM t GROUP BY age-1;
ANY_VALUE() can be used for queries that refer to aggregate functions in the absence of a GROUP BY clause:
mysql> SELECT name, MAX(age) FROM t;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression
#1 of SELECT list contains nonaggregated column 'mydb.t.name'; this
is incompatible with sql_mode=only_full_group_by
Without GROUP BY, there is a single group and it is indeterminate which name value to choose for the group. ANY_VALUE() tells MySQL to accept the query:
SELECT ANY_VALUE(name), MAX(age) FROM t;
It may be that, due to some property of a given data set, you know that a selected nonaggregated column is effectively functionally dependent on a GROUP BY column. For example, an application may enforce uniqueness of one column with respect to another. In this case, using ANY_VALUE() for the effectively functionally dependent column may make sense.
For additional discussion, see Section 13.19.3, “MySQL Handling of GROUP BY”.

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台