標準 SQL では、GROUP BY
句を含むクエリーは、GROUP BY
句で名前が指定されていない選択リスト内の非集約カラムを参照できません。たとえば、このクエリーは、選択リスト内の
name
カラムが GROUP BY
に表示されていないため、標準 SQL
では不正です。
SELECT o.custid, c.name, MAX(o.payment)
FROM orders AS o, customers AS c
WHERE o.custid = c.custid
GROUP BY o.custid;
このクエリーを正当にするには、name
カラムを選択リストから削除するか、GROUP
BY
句で名前を指定する必要があります。
MySQL では、選択リストが GROUP BY
句で名前が指定されていない非集約カラムを参照できないように、GROUP
BY
の使用が拡張されています。つまり、上記のクエリーは
MySQL
では正当です。この機能を使用すると、不要なカラムのソートおよびグループ化が回避されるため、パフォーマンスを改善できます。ただし、これは主に、GROUP
BY
で名前が指定されていない各非集約カラム内のすべての値がグループごとに同じである場合に役立ちます。サーバーは各グループから任意の値を自由に選択できるため、同じ値でなければ、選択した値は不確定です。さらに、ORDER
BY
句を追加しても、各グループからの値の選択が影響を受ける可能性はありません。値が選択されたあとに結果セットのソートが発生しますが、ORDER
BY
によって、サーバーで選択された各グループ内の値は影響を受けません。
同様の MySQL 拡張が HAVING
句に適用されます。標準 SQL では、GROUP
BY
句を含むクエリーは、GROUP
BY
句で名前が指定されていない
HAVING
句の非集約カラムを参照できません。MySQL
拡張では、計算を簡単にするために、このようなカラムへの参照が許可されます。この拡張では、グループ化されていないカラムに同じグループに関する値が含まれると仮定されます。それ以外の場合は、結果が不確定です。
MySQL GROUP BY
の拡張を無効にするには、ONLY_FULL_GROUP_BY
SQL モードを有効にします。これにより、標準
SQL の動作が有効になります。GROUP
BY
句で名前が指定されていないカラムは、集約関数で囲まなければ、選択リストまたは
HAVING
句で使用できません。
また、ONLY_FULL_GROUP_BY
によって、HAVING
句のエイリアスの使用も影響を受けません。たとえば、次のクエリーは、orders
テーブルで 1 回だけ発生する name
値を返します。
SELECT name, COUNT(name) FROM orders
GROUP BY name
HAVING COUNT(name) = 1;
MySQL では、集約カラム用に HAVING
句でエイリアスを使用することが許可されるように、この動作が拡張されています。
SELECT name, COUNT(name) AS c FROM orders
GROUP BY name
HAVING c = 1;
ONLY_FULL_GROUP_BY
を有効にすると、この MySQL
拡張が無効になり、HAVING
句の
c
カラムが集約関数で囲まれていないため
(これは、集約関数です)、「non-grouping
field 'c' is used in HAVING
clause」
というエラーが発生します。
選択リストの拡張は、ORDER BY
にも適用されます。つまり、GROUP
BY
句に表示されていない ORDER
BY
句の非集約カラムは参照できません。(ただし、すでに説明したように、ORDER
BY
によって、非集約カラムからどの値が選択されるのかは影響を受けません。選択されたあとにはじめてソートされます。)ONLY_FULL_GROUP_BY
SQL
モードが有効になっている場合は、この拡張が適用されません。
一部のケースでは、MIN()
および MAX()
を使用すると、一意でない場合でも特定のカラム値を取得できます。sort
カラムに 6
桁以内の整数が含まれている場合、次のクエリーは、最小の
sort
値を含む行から
column
の値を取得します。
SUBSTR(MIN(CONCAT(LPAD(sort,6,'0'),column)),7)
セクション3.6.4「特定のカラムのグループごとの最大値が格納されている行」を参照してください。
標準 SQL に従おうとすると、GROUP
BY
句で式を使用できません。回避策として、式のエイリアスを使用します。
SELECT id, FLOOR(value/100) AS val
FROM tbl_name
GROUP BY id, val;
MySQL では、GROUP BY
句で式を使用することが許可されているため、エイリアスは必要ありません。
SELECT id, FLOOR(value/100)
FROM tbl_name
GROUP BY id, FLOOR(value/100);