LoginSignup
0
1

More than 1 year has passed since last update.

@wacky3(wacky3)in株式会社リーディング・エッジ社

SQLZooの self join の問題を解いてみた。

Last updated at Posted at 2021-12-12

知り合いが、SQLの勉強の為に SQLZoo というサイトを参考にしていました。
ただ、Self Join のページがわからないとの事で、この記事を作成することにしました。

ネット上には同様な解説サイトが多数ありますが、今回は以下のサイトを参考にさせていただきました。

では、問題を考えていきます。

Edinburgh Buses. (エジンバラのバス)

テーブル定義は以下のページにあります。
https://sqlzoo.net/wiki/Edinburgh_Buses.

テーブル：stops

カラム名 型 備考
id INT 任意の値
name CHAR(30) バスが運行しているエリア・地域

※バスが運用しているエリアのリスト。
　実際のバス停名は含まれない。

テーブル ：route

カラム名 型 備考
num CHAR(5) バス系統番号
company CHAR(3) バス会社名
pos INT ルート内停止順※1
stop INT stopsテーブルのid

※バスで街を通過する記録
※1、一部のルートでは、停車地を再訪する場合があります。
　　　ほとんどのバスは両方向に行きます。

問題１

テーブル：stopsのレコード数は？

 select count(*) from stops;

答え
　246個

では、テーブル：routeのレコード数は？

 select count(*) from route;

1174個

問題２

バスが止まるエリア'Craiglockhart'のidは？

　select id from stops where name = 'Craiglockhart'

答えは

53

では、id が 246 の停車エリアは？

  select name from stops where id = 246;

答えは
Whitburn

問題３

原文
Give the id and the name for the stops on the '4' 'LRT' service.
概約
サービス(バス会社とバス系統番号)が LRT と 4 の 止まる id と エリア名は？

ここでは、routeテーブルからバス会社とバス系統番号を検索し止まる番号を求める。
その結果を stopsテーブルのidとnameを求める。

よって、先ずテーブルを結合することを考える。
routeテーブルのstopは、stopsテーブルのid になっているので以下の様に結合(join)してみる。

select * from route join stops on id = stop;

num company pos stop    id  name
124 SMT 9   1   1   Aberlady
100 MAC 6   2   2   Abington
106 SMT 8   3   3   Amisfield Park
29  LOW 8   4   4   Ancrum

ここから、問題３の条件を当てはめてSQLを作る。

select id, name from stops join route on id = stop
 where num = 4 and company = 'LRT';

と書くと答えが正解にならない。
正しくは、

select id, name from stops join route on id = stop
 where num = '4' and company = 'LRT';

となる。
routeテーブルのnumは数値と思って ’’ でくくらないと答えはでるが正解にはならない。

バスの路線と停車エリア

問題４

回答欄に示されているクエリは、London Road(149)または Craiglockhart(53)のいずれかを訪れるルートの数を示しています。

SELECT company, num, COUNT(*)
 FROM route WHERE stop=149 OR stop=53
 GROUP BY company, num

クエリを修正し、これらの停車エリア を通過する 2つのサービス(company と num )のカウントが2であるものを抽出してください。
尚、HAVING句を追加して、出力をこれら2つのルートにあるものに制限します。

ちなみに、回答欄に示されているクエリをそのまま実行すると

company num COUNT(*)
LRT 10  1
LRT 15  1
LRT 20  1
LRT 26A 1
LRT 27  1
LRT 34  1
LRT 35  1
LRT 4   2
LRT 42  1
LRT 43  1
LRT 44  1
LRT 45  2
LRT 46A 1
LRT 47  1
LRT 5   1
LRT 51  1
LRT 63  1
LRT 65  1
LRT 75  1
LRT 87  1
LRT 87A 1
SMT 106 1
SMT 113 1
SMT 124 1
SMT 66  1
SMT 66A 1
SMT C5  1
SMT C55 1

と表示される。

では、いったい何行あるのかと思ってクエリを作成します。
compayの数を副問合せ(in)を使用してみます。

SELECT count(*) from route 
 where company in
( select company
FROM route WHERE stop=149 OR stop=53
GROUP BY company, num )

1009行あることが確認できる。

既にcount(*)が 2 であるものが目視できるが他にもある可能性があるのでクエリを修正すると

SELECT company, num, COUNT(*)
FROM route WHERE stop=149 OR stop=53
GROUP BY company, num
having count(*) = 2;

company num COUNT(*)
LRT 4   2
LRT 45  2

となる。

問題５

示されている自己結合を実行し、b.stopがルートを変更せずにCraiglockhartから到達できるすべての場所を提供することを確認します。
クエリを変更して、CraiglockhartからLondonRoadまでのサービスが表示されるようにします。
（Google翻訳より）

※提示されているクエリ
SELECT a.company, a.num, a.stop, b.stop
FROM route a JOIN route b ON
  (a.company=b.company AND a.num=b.num)
WHERE a.stop=53

を実行すると

company num stop    stop
LRT 10  53  215
LRT 10  53  162
　：
　：
　：
LRT 27  53  59
LRT 27  53  37
　：
　：

となる。

自己結合(1つ目のrouteを a とし 2つ目のrouteを b とする )の要件を確認すると、aとbのバス会社(company) と aとbのバス系統番号(num)が同じものの表を作成している。
結果として、同じ会社で同じバス系統番号のバスが止まる表になる。
ここで、where句を付けないと、停車エリア(stop)の組み合わせが大量にできて大きな表になる。
　問題にある停車エリア(Craiglockhart)は、問題４から、stop = 53 と判明しているので、例題の絞り込みに指定することで、停車エリア(Craiglockhart)に関連する一覧を表示する。

さて、問題の停車エリア(LondonRoad)は stop = 149 なので 自己結合した b テーブルの stop を絞り込めばよい。

SELECT a.company, a.num, a.stop, b.stop
 FROM route a JOIN route b ON
  (a.company=b.company AND a.num=b.num)
 WHERE a.stop=53 and b.stop=149 ;

結果は

company num stop    stop
LRT 4   53  149
LRT 45  53  149

となり、バス会社 LRT の バス系統番号の4番と45番 が 停車エリア(Craiglockhart)と停車エリア(LondonRoad)を通過することがわかる。

問題６

示されているクエリは前のクエリと似ていますが、ストップテーブルの2つのコピーを結合することにより、番号ではなく名前でストップを参照できます。
'Craiglockhart'と 'London Road'の間のサービスが表示されるようにクエリを変更します。
これらの場所にうんざりしている場合は、「Tollcross」に対して「Fairmilehead」を試してください。
（Google翻訳より）

※提示されているクエリ
SELECT a.company, a.num, stopa.name, stopb.name
FROM route a JOIN route b ON
  (a.company=b.company AND a.num=b.num)
  JOIN stops stopa ON (a.stop=stopa.id)
  JOIN stops stopb ON (b.stop=stopb.id)
WHERE stopa.name='Craiglockhart'

これは 問題５の routeテーブルの自己結合に stopテーブルをさらに結合する。
　1つ目のroute a テーブルには、stopsテーブルを stopsa として 結合する。
　2つ目のtoute b テーブルには、stopsテーブルを stopsb として 結合する。
これで、route a b それぞれの停車エリアの番号であったものを停車エリア名で表示する。

提示されているクエリをそのまま実行すると、

company num name    name
LRT 10  Craiglockhart   Silverknowes
LRT 10  Craiglockhart   Muirhouse
　：
　：
LRT 27  Craiglockhart   Silverknowes
LRT 27  Craiglockhart   Crewe Toll
　：
　：

となる。

問題にあるように、'Craiglockhart'と 'London Road'の間のサービス(バス会社とバス系統番号)が表示されるよう変更するには、問題5と同じ様に絞り込みの条件を追加すればよい。

SELECT a.company, a.num, stopa.name, stopb.name
FROM route a JOIN route b ON
  (a.company=b.company AND a.num=b.num)
  JOIN stops stopa ON (a.stop=stopa.id)
  JOIN stops stopb ON (b.stop=stopb.id)
WHERE
  stopa.name='Craiglockhart'
  and 
  stopb.name='London Road';

結果は

company num name    name
LRT 4   Craiglockhart   London Road
LRT 45  Craiglockhart   London Road

問題７

原文
Give a list of all the services which connect stops 115 and 137 ('Haymarket' and 'Leith')
概約
停車エリア番号 115と137（「Haymarket」と「Leith」）を接続するすべてのサービス（バス会社とバス系統番号）のリストを提供します。
(Google翻訳より)

問題６を利用して、

SELECT a.company, a.num
FROM route a JOIN route b ON
  (a.company=b.company AND a.num=b.num)
  JOIN stops stopa ON (a.stop=stopa.id)
  JOIN stops stopb ON (b.stop=stopb.id)
WHERE
  stopa.name='Haymarket'
  and 
  stopb.name='Leith';

結果は
company num
LRT 12
LRT 12
LRT 2
LRT 2
LRT 22
LRT 25
LRT 2A
LRT 2A
SMT C5

となる。
重複しているレコードがあるので問題のリストとしては不適切になる。
重複を排除するために、DISTINCT 句を使用します。

SELECT DISTINCT a.company, a.num
FROM route a JOIN route b ON
  (a.company=b.company AND a.num=b.num)
  JOIN stops stopa ON (a.stop=stopa.id)
  JOIN stops stopb ON (b.stop=stopb.id)
WHERE
  stopa.name='Haymarket'
  and 
  stopb.name='Leith';

結果
company num
LRT 12
LRT 2
LRT 22
LRT 25
LRT 2A
SMT C5

問題８

原文
　Give a list of the services which connect the stops 'Craiglockhart' and 'Tollcross'
概約
　ストップ「クレイグロックハート」と「トールクロス」を結ぶサービスのリストを提供します。

これも問題７を利用してクエリーを作成すると

SELECT DISTINCT a.company, a.num
FROM route a JOIN route b ON
  (a.company=b.company AND a.num=b.num)
  JOIN stops stopa ON (a.stop=stopa.id)
  JOIN stops stopb ON (b.stop=stopb.id)
WHERE
  stopa.name='Craiglockhart'
  and 
  stopb.name='Tollcross';

結果は

company num
LRT 10
LRT 27
LRT 45
LRT 47

となります。

問題９

原文
　Give a distinct list of the stops which may be reached from 'Craiglockhart' by taking one bus, including 'Craiglockhart' itself, offered by the LRT company. Include the company and bus no of the relevant services.
概約
LRT会社が提供する「クレイグロックハート」自体を含む1つのバスに乗ることで、「クレイグロックハート」から到達できる停車地の明確なリストを提供します。 関連するサービスの会社とバス番号を含めます。
(Google翻訳より)

これも、前回のクエリーを利用して考えますが、前回と違うところは、
　必要なリストは、
　「クレイグロックハート」から到達できる停車地の明確なリスト、サービス（バス会社名とバス系統番号）
　絞り込み条件は
　・バス会社はLRT
　・バス停車エリア'Craiglockhart'を通過すること
　・ 'Craiglockhart'を通過するバス停車エリアをすべて

上記の内容をクエリーにすると

SELECT DISTINCT stopb.name, a.company, a.num
FROM route a JOIN route b ON
  (a.company=b.company AND a.num=b.num)
  JOIN stops stopa ON (a.stop=stopa.id)
  JOIN stops stopb ON (b.stop=stopb.id)
WHERE
  stopa.name='Craiglockhart'

結果は

name    company num
Silverknowes    LRT 10
Muirhouse   LRT 10
Newhaven    LRT 10
Leith   LRT 10
Leith Walk  LRT 10
Princes Street  LRT 10
Tollcross   LRT 10
Craiglockhart   LRT 10
Colinton    LRT 10
Torphin LRT 10
Silverknowes    LRT 27
Crewe Toll  LRT 27
Canonmills  LRT 27
Hanover Street  LRT 27
Tollcross   LRT 27
Craiglockhart   LRT 27
Oxgangs LRT 27
Hunters Tryst   LRT 27
Bingham LRT 4
Northfield  LRT 4
London Road LRT 4
Princes Street  LRT 4
Haymarket   LRT 4
Craiglockhart   LRT 4
Oxgangs LRT 4
Fairmilehead    LRT 4
Hillend LRT 4
Brunstane   LRT 45
Duddingston LRT 45
Northfield  LRT 45
London Road LRT 45
Hanover Street  LRT 45
Tollcross   LRT 45
Craiglockhart   LRT 45
Colinton    LRT 45
Currie  LRT 45
Riccarton Campus    LRT 45
Canonmills  LRT 47
Hanover Street  LRT 47
Tollcross   LRT 47
Craiglockhart   LRT 47
Colinton    LRT 47
Currie  LRT 47
Balerno LRT 47
Cockburn Crescent   LRT 47
Balerno Church  LRT 47

問題１０

原文
Find the routes involving two buses that can go from Craiglockhart to Lochend.
Show the bus no. and company for the first bus, the name of the stop for the transfer,
and the bus no. and company for the second bus.

Hint
Self-join twice to find buses that visit Craiglockhart and Lochend, then join those on matching stops.

概約
クレイグロックハートからロッヘンドまで行くことができる2つのバスを含むルートを見つけます。
最初のバスのバス番号と会社、乗り換えの停留所の名前、2番目のバスのバス番号と会社を表示します。

ヒント
クレイグロックハートとロッヘンドを訪れるバスを見つけるために2回自己参加してから、一致する停留所でそれらに参加します。
(Google翻訳より)

これも、前回のクエリーを利用して考えますが、前回と違うところは、
　必要なリストは、
　　最初のバス系統番号とバス会社、乗り換えのバス停車エリア、その次のバス系統番号とバス会社
　絞り込み条件は
　　最初のスタートエリアは 'Craiglockhart'
　　目的地点のエリアは 'Lochend'
　　中継エリアがある、
　　　・'Craiglockhart' から 中継エリアまで
　　　・中継エリア から 'Lochend' まで
　　＝＝＞
　　前問は スタートからゴールまで乗り換えなしですが、今回は乗り換えがあるため2回考える必要がある。
　　ただし、1回目のゴールと2回目のスタートが同じとすればいい。

感じとしては、問題８を2回分まとめて条件づけする感じです。

一つ目は

SELECT DISTINCT a.company, a.num
FROM route a JOIN route b ON
  (a.company=b.company AND a.num=b.num)
  JOIN stops stopa ON (a.stop=stopa.id)
  JOIN stops stopb ON (b.stop=stopb.id)
WHERE
  stopa.name='Craiglockhart'
  and 
  stopb.name= 中継エリア

二つ目は

SELECT DISTINCT c.company, c.num
FROM route c JOIN route d ON
  (c.company=d.company AND c.num=d.num)
  JOIN stops stopc ON (c.stop=stopc.id)
  JOIN stops stopd ON (d.stop=stopd.id)
WHERE
  stopc.name= 中継エリア
  and 
  stopd.name='Lochend'

を一つにまとめると

SELECT DISTINCT a.num, a.company, stopb.name, c.num, c.company 
FROM route a 
  JOIN route b ON (a.company=b.company AND a.num=b.num)
  JOIN (route c join route d ON (c.company=d.company AND c.num=d.num))
  JOIN stops stopa ON (a.stop=stopa.id)
  JOIN stops stopb ON (b.stop=stopb.id)
  JOIN stops stopc ON (c.stop=stopc.id)
  JOIN stops stopd ON (d.stop=stopd.id)
WHERE
  stopa.name='Craiglockhart'
  and stopb.name=stopc.name;
  and stopd.name='Lochend'

結果は

num company name    num company
10  LRT Leith   34  LRT
10  LRT Leith   35  LRT
10  LRT Leith   87  LRT
10  LRT Leith   C5  SMT
10  LRT Princes Street  65  LRT
10  LRT Princes Street  C5  SMT
27  LRT Crewe Toll  20  LRT
27  LRT Canonmills  34  LRT
27  LRT Canonmills  35  LRT
4   LRT London Road 20  LRT
4   LRT London Road 34  LRT
4   LRT London Road 35  LRT
4   LRT London Road 42  LRT
4   LRT London Road 46A LRT
4   LRT London Road 65  LRT
4   LRT London Road 87  LRT
4   LRT London Road 87A LRT
4   LRT London Road C5  SMT
4   LRT Princes Street  65  LRT
4   LRT Princes Street  C5  SMT
4   LRT Haymarket   65  LRT
4   LRT Haymarket   C5  SMT
45  LRT Duddingston 42  LRT
45  LRT Duddingston 46A LRT
45  LRT London Road 20  LRT
45  LRT London Road 34  LRT
45  LRT London Road 35  LRT
45  LRT London Road 42  LRT
45  LRT London Road 46A LRT
45  LRT London Road 65  LRT
45  LRT London Road 87  LRT
45  LRT London Road 87A LRT
45  LRT London Road C5  SMT
45  LRT Riccarton Campus    65  LRT
47  LRT Canonmills  34  LRT
47  LRT Canonmills  35  LRT

となりますが、正解にはなりません。
正解と内容は同じで並びが合っていないので、

ORDER BY a.num, stopb.name, c.num;

を付けて

SELECT DISTINCT a.num, a.company, stopb.name, c.num, c.company 
FROM route a 
  JOIN route b ON (a.company=b.company AND a.num=b.num)
  JOIN (route c join route d ON (c.company=d.company AND c.num=d.num))
  JOIN stops stopa ON (a.stop=stopa.id)
  JOIN stops stopb ON (b.stop=stopb.id)
  JOIN stops stopc ON (c.stop=stopc.id)
  JOIN stops stopd ON (d.stop=stopd.id)
WHERE
  stopa.name='Craiglockhart'
  and stopb.name=stopc.name;
  and stopd.name='Lochend'
ORDER BY a.num, stopb.name, c.num;

とすることで、正解になる。

0
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
1