94 views
1. やりたいこと
以下のようなテーブル構成のシステムを作っているとする。
このとき、membershipテーブルから指定したクラブ(club)に所属している人(person)を検索し、人の名前とクラブ名を並べて表示したい。
各テーブルの仕様は以下の通り。
・personテーブルは、人の識別番号(pid)と人の名前(pname)を持つ。
・clubテーブルは、クラブの識別番号(cid)とクラブ名(cname)を持つ。
・membershipテーブルは、クラブの識別番号と人の識別番号だけを持つ。
よって、複数のテーブルを横断して情報収集する必要がある。
これを 1回のクエリ発行で実現したい。
2. やってみる
(1) 基本：指定したクラブに所属している人の情報を結合表示する。
テーブルの結合には JOIN句を使う。
ここでは、剣道部(cid=3)に所属している人を検索してみる。
SQLを実行した結果、赤枠内の 3人がヒットするはず。
SELECT club.cid, club.cname, person.pid, person.pname FROM membership JOIN club ON membership.cid=club.cid AND membership.cid='3' JOIN person ON membership.pid=person.pid AND membership.cid='3'
OKだ。意図したとおりに取得できている。
この SQL文を ChatGPTに解説してもらった。
あまりにも解説が上手いのでそのまま貼り付ける。
FROM句：
クエリの主体となるmembershipテーブルからデータを取得します。このテーブルはおそらく、クラブと人物の関連（つまり、どの人がどのクラブに所属しているか）を表すためのテーブルです。
JOIN句（最初のJOIN）：
membershipテーブルとclubテーブルを結合します。この結合はmembership.cid=club.cidという条件に基づいて行われ、membershipテーブルのcid（クラブID）とclubテーブルのcidが一致する行を結合します。加えて、membership.cid=’3’という条件で、IDが’3’のクラブに所属するメンバーに限定しています。
JOIN句（2番目のJOIN）：
membershipテーブルとpersonテーブルを結合します。この結合はmembership.pid=person.pidという条件に基づいて行われ、membershipテーブルのpid（人物ID）とpersonテーブルのpidが一致する行を結合します。AND membership.cid=’3’の条件は、前述の通り、IDが’3’のクラブに所属するメンバーに限定しています。
SELECT句：
選択されたカラムとして、club.cid（クラブID）、club.cname（クラブ名）、person.pid（人物ID）、person.pname（人物名）が挙げられています。これにより、IDが’3’のクラブに所属する全ての人物のIDと名前、およびそのクラブのIDと名前が取得されます。
結果として、このクエリは「IDが’3’のクラブに所属する全ての人物の情報と、そのクラブの情報」を返します。これにより、特定のクラブに所属するメンバーの一覧を得ることができます。
(2) 応用：複数のクラブに所属している人を抽出する。
複数のクラブに所属しているのは pid=2 の佐藤だけだ。
SELECT person.pid, person.pname FROM membership JOIN person ON membership.pid=person.pid GROUP BY membership.pid HAVING COUNT(DISTINCT membership.cid)=2
OKだ。意図したとおりに取得できている。
この SQL文も ChatGPTに解説してもらおう。
あまりにも解説が上手いのでそのまま貼り付ける。
FROM句：
クエリはmembershipテーブルから始まります。このテーブルはおそらく、人々がどのクラブに所属しているかを示すためのものです（pidは人物を、cidはクラブを識別するためのカラムでしょう）。
JOIN句：
次に、JOINを使用してmembershipテーブルとpersonテーブルを結合します。結合条件はmembership.pid=person.pidです。これにより、membershipテーブルの各レコードに対応するpersonテーブルのレコードが結合され、人物の詳細情報を取得できます。
SELECT句：
SELECT句では、person.pid（人物ID）とperson.pname（人物名）を選択しています。これにより、最終的な結果セットにこれらのカラムが含まれます。
GROUP BY句：
クエリはmembership.pidに基づいてデータをグループ化します。つまり、同じpidを持つレコードは一つのグループにまとめられます。これにより、個々の人物が複数のクラブに所属している場合にも、その人物は一つのグループに集約されます。
HAVING句：
最後に、HAVING COUNT(DISTINCT membership.cid) = 2という条件があります。この条件は、グループ化されたデータに対して適用され、各人物が異なるクラブに2回所属している（つまり、membership.cidのユニークな値が2つある）場合にのみ、その人物のデータを結果セットに含めるよう指定しています。
結果として、このクエリは「2つの異なるクラブに所属している人物のIDと名前」を返します。これは、例えば、人々が複数のクラブ活動に参加している状況を分析する場合に有用です。