はじめに

こんにちわ、千秋です。

先日、担当する業務でSQLの知識が必要となったため、勉強のために「達人に学ぶSQL徹底指南書（2008年出版）」を読み始めました。

しかし、記載されているコード通りに実行すると表題のエラー（Error Code: 1055）が発生しました。これの解決方法を本日は共有しようと思います。

使用するSQL言語

MySQL WorkBench 8.0

本題

表について

次のような表（studentclub）があります。

学生はクラブに所属している
学生は複数のクラブに所属していることがある
学生が一つのクラブにしか所属していない場合、main_club_flgはtrue
学生が複数のクラブにしか所属している場合、main_club_flgはfalse

表を作成する場合は、下記SQLを実行してください。

studentclubのCREATE文、INSERT文


-- 複数のクラブに属している場合、主に活動している部活動のレコードのmain_club_flgが1になる
-- 一つだけに属している場合、main_club_flgは0になる
CREATE TABLE `studentclub` (
  `std_id` int NOT NULL,
  `club_id` int DEFAULT NULL,
  `club_name` varchar(45) DEFAULT NULL,
  `main_club_flg` tinyint DEFAULT NULL
) 

INSERT INTO  studentclub (std_id, club_id,club_name,main_club_flg)
VALUES 
(100, 1, '野球', true),
(100, 2, '吹奏楽', false),
(200, 2, '吹奏楽', false),
(200, 3, 'バドミントン', true),
(200, 4, 'サッカー', false),
(300, 4, 'サッカー', false),
(400, 5, '水泳', false),
(500, 6, '囲碁', false);

期待するSELECT結果

この表に対して、次のようなSQLを作成したいです。

１つだけのクラブに所属している学生については、そのクラブIDを取得する
複数のクラブを掛け持ちしている学生については、主なクラブのIDを取得する

エラーとなった原因

そこで、次のSQLを作成しましたが、Errorとなりました。

SELECT 
    std_id, 
    case when count(*) = true then club_id
    ELSE MAX( case when main_club_flg = true then club_id
					else null end
    )
    end as main_club
FROM
    studentclub
group by std_id

Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause
 and contains nonaggregated column 'world.studentclub.club_id' which is not 
 functionally dependent on columns in GROUP BY clause; 
 this is incompatible with sql_mode=only_full_group_by

読み砕くと、「group by句で指定していないカラムをSELECTで指定してはいけない」ということです。SELECT句で指定するのは集計対象のカラム、つまりgroup by句で指定するカラムのみとなるようです。

設定値の「ONLY_FULL_GROUP_BY」を無効にすると、上記のSQLでも実行結果が得られるそうですが、標準の設定値をいじることは極力したくないですね。

解決方法：ANY_VALUE()

ANY_VALUE()でgroup by句で含めないカラムを指定することで、そのカラムを非集計カラムとして扱うことができます。これにより、SQLを問題なく実行できるようになりました。

SELECT 
    std_id, 
    case when count(*) = true then any_value( club_id)
    ELSE MAX( case when main_club_flg = true then any_value( club_id)
					else null end
    )
    end as main_club
FROM
    studentclub
group by std_id

終わりに

以上となります。

標準ドキュメントに記載してありますが、group by句で指定していなくても、関数従属性がある場合はSELECT句で指定できるなど、色々と細かい条件があるようです。是非そちらもご一読ください。

参考