MYSQL における GRPUP BY メモ

最終更新日:

このようなテーブルがあったとする

a_table

  • id
  • name

b_table

  • id
  • a_table_id
  • name

このようなデータがあったとする

a_table

INSERT INTO `a_table` (`id`, `name`) VALUES
(1, 'あ'),
(2, 'い'),
(3, 'う'),
(4, 'え'),
(5, 'お');

b_table

INSERT INTO `b_table` (`id`, `a_table_id`, `name`) VALUES
(1, 3, 'three'),
(2, 5, 'five'),
(3, 3, 'three 2'),
(4, 100, 'one hundred');

構文エラー

下記 SQL文はエラーになる。

Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mysql_sample.b.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

SELECT a.*, b.a_table_id, b.name as b_name 
FROM a_table as a
LEFT OUTER JOIN b_table as b ON a.id = b.a_table_id 
WHERE 1 
GROUP BY a.id;

ざっくり読んだ感じ、
GROUP BY でまとめたいんだけど、
どのレコードを選択するべきか判断できない場合エラーになる。

上記のエラーは、
GROUP BY a.id したいんだけど、
'mysql_sample.b.name' がその状態になっていたため起きたエラー。

b_table (id, a_table_id, name) の
(1, 3, 'three'),(3, 3, 'three 2') のどちらを結果として出力すればいいか、
明確に示されていない。

対応として、
SELECT からその原因（'mysql_sample.b.name'）を取り除くか、
MAX() や MIN()、 SUM() などで明確にする。

サンプル

'mysql_sample.b.name' を SELECT から除く

SELECT a.*, b.a_table_id 
FROM a_table as a
INNER JOIN b_table as b ON a.id = b.a_table_id 
WHERE 1 
GROUP BY a.id;
id name a_table_id
3 う 3
5 お 5

'mysql_sample.b.name' を MAX() や MIN() で指定

MAX(), MIN() は文字列にも使えるみたい。

SELECT a.*, b.a_table_id, MAX(b.name) as b_name 
FROM a_table as a 
INNER JOIN b_table as b ON a.id = b.a_table_id 
WHERE 1 
GROUP BY a.id;
id name a_table_id b_name
3 う 3 three 2
5 お 5 five

SELECT に存在していないカラムを指定できる

出力する内容に問題なければ、
SELECT に存在していなくてもエラーにならない。

SELECT a.name, b.a_table_id, MAX(b.name) as b_name 
FROM a_table as a
LEFT OUTER JOIN b_table as b ON a.id = b.a_table_id 
WHERE 1 
GROUP BY a.id;
name a_table_id b_name
あ NULL NULL
い NULL NULL
う 3 three 2
え NULL NULL
お 5 five

取得したレコードの値がバラバラになる

あんまり想像できないパターンだけど、
こういうのもできる。

SELECT a.*, MIN(b.id), b.a_table_id, MAX(b.name) 
FROM a_table as a
LEFT OUTER JOIN b_table as b ON a.id = b.a_table_id 
WHERE 1 
GROUP BY a.id;

本来 b.id = 1 の時、 b.name は three 2 ではなく three。
それぞれの箇所に MIN() と MAX() を併用したため。
ある意味正しい結果が取得できている。

id name MIN(b.id) a_table_id MAX(b.name)
1 あ NULL NULL NULL
2 い NULL NULL NULL
3 う 1 3 three 2
4 え NULL NULL NULL
5 お 2 5 five