宣言
ANY_VALUE(arg)
説明
sql_mode が ONLY_FULL_GROUP_BY SQLモードを有効にした場合、この関数は FULL GROUP BY でない操作を実行する際に使用され、ONLY_FULL_GROUP_BY の制約によるクエリの拒否を抑制します。
ANY_VALUE() は、同じグループに分類されたデータのうち、最初のデータの指定された列の値を返します。関数の戻り値と型は、引数の戻り値と型と同じです。
以下の例では、name が非インデックス列であり、ONLY_FULL_GROUP_BY SQL モードが有効な場合、GROUP BY クエリは失敗します。これは、city が集約されていない列であるため、各 name グループ内の city 値が不確定であるためです。
obclient> CREATE TABLE tbl1(name VARCHAR(20),age VARCHAR(10),city VARCHAR(50));
obclient> INSERT INTO tbl1 VALUES ('Alex',20,'BeiJing'),('Jim',25,'HangZhou'),('Blair',35,'NanJing');
obclient> SET sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected
obclient> SELECT name, city, MAX(age) FROM tbl1 GROUP BY name;
ERROR 1055 (42000): 'test.tbl1.city' is not in GROUP BY
解決策の一つは、テーブルを変更して name を主キーまたは一意の NOT NULL 列にすることです。これにより、city は name によって一意に決定されます（有効な name 値として NULL を許可する必要がある場合は、この方法は適用できません）。
もう一つの解決策は、ANY_VALUE() を使用して city を参照することです。これにより、各 name グループ内の city 値の不確定性を無視し、クエリを受け入れることができます。ANY_VALUE() は集約関数ではなく、不確定なテストを抑制する役割を果たすだけです。
obclient> SELECT name, ANY_VALUE(city), MAX(age) FROM tbl1 GROUP BY name;
+-------+-----------------+----------+
| name | ANY_VALUE(city) | MAX(age) |
+-------+-----------------+----------+
| Alex | BeiJing | 20 |
| Jim | HangZhou | 25 |
| Blair | NanJing | 35 |
+-------+-----------------+----------+
3 rows in set
別の方法は、ONLY_FULL_GROUP_BY を無効にすることです。これは、上述の ANY_VALUE() を使用する場合と同じになります。
例
GROUP BY 句がない場合、グループが1つしかない場合にそのグループでどの name 値を選択するかは不確定であるため、クエリは失敗します。しかし、ANY_VALUE() はこの問題を回避し、データベースが集約関数を参照するクエリを受け入れることを可能にします。
obclient> SELECT name, MAX(age) FROM tbl1;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
obclient> SELECT ANY_VALUE(name), MAX(age) FROM tbl1;
+-----------------+----------+
| ANY_VALUE(name) | MAX(age) |
+-----------------+----------+
| Alex | 35 |
+-----------------+----------+
1 row in set