[MySQL] 1対多で紐づくテーブルが複数あった場合の「group_concat」
- 2019年9月26日
- Web Service
今回は1対多で紐づくテーブルが複数あった場合の「group_concat」の使用方法についてです。
以下のように3つのテーブルがあります。
各テーブルはclassテーブルのidをkeyとして紐付けられています。
classテーブル
studentテーブル
itemテーブル
まずは各クラスに属している生徒名を調べるために
「group_concat」を使用して以下のSQLを作成しました。
select
a.id
, a.classname
, group_concat(b.name)
from
class a
left join students b
on a.id = b.classid
group by
a.id;
上記の実行結果:
これで各クラスに属する生徒がわかりました。
次に各クラスが所有する備品を調べるために
以下のSQLを作成しました。
こちらも「group_concat」を使用しています。
select
a.id
, a.classname
, group_concat(b.name)
from
class a
left join items b
on a.id = b.classid
group by
a.id;
上記の実行結果:
これで各クラスが所有する備品がわかりました。
それでは、各クラスに属する生徒と各クラスが所有する備品を
同時に調べたい場合、どうすればいいでしょうか。
単純に上記の2つのSQLをミックスしたらどうなるか試してみました。
select
a.id
, a.classname
, group_concat(c.name)
, group_concat(b.name)
from
class a
left join items b
on a.id = b.classid
left join students c
on a.id = c.classid
group by
a.id;
上記の実行結果:
出力結果に同じ名称が重複して表示されてしまいました。
重複箇所を一意にするには「distinct」を使用すれば改善できるかもしれません。
上記のSQLに「distinct」を追加して出力してみました。
select
a.id
, a.classname
, group_concat(distinct c.name)
, group_concat(distinct b.name)
from
class a
left join items b
on a.id = b.classid
left join students c
on a.id = c.classid
group by
a.id;
上記の実行結果
うまくいったようです。
次にdistinctを使用しないで実現する方法はないかを検討しました。
サブクエリを使って出力させた場合はどうでしょうか。
以下、試してみました。
select
a.id
, a.classname
, group_concat(distinct b.name)
from
class a
left join students b
on a.id = b.classid
group by
a.id;
上記のSQLを以下のようにサブクエリを使用するSQLに
変形しました。
select
a.id
, a.classname
, (
select
group_concat(name)
from
students
where
classid = a.id
) studentname
from
class a;
上記の実行結果:
変形前と同じ出力結果で出力できることがわかりました。
次に、サブクエリを使った方法で、備品を確認するための
SQLを追加してみました。
select
a.id
, a.classname
, (
select
group_concat(name)
from
students
where
classid = a.id
) studentname
, (
select
group_concat(name)
from
items
where
classid = a.id
) itemname
from
class a;
上記の実行結果:
この方法で結果が重複することなく正しい結果が出力できました。
サブクエリを使って対応することも可能です。
以上、1対多で紐づくTableが複数あった場合の「group_concat」
の使用方法について紹介いたしました。
木曜日担当: nishida
nishida at 2019年09月26日 10:00:57