Self join/ja

エディンバラのバス

データベースの詳細 データをながめる

停留所 stops(id, name)
路線 route(num, company, pos, stop)

停留所stops
番号id
駅名name

路線route
番号num
会社company
順番pos
停留所番号stop

---

要約

データベースに停留所stopsはいくつあるか。

SELECT COUNT(*) 
FROM stops

クレイグロックハート'Craiglockhart'のidの値を見つける。

SELECT id 
FROM stops 
WHERE name='Craiglockhart'

'LRT'社の'4'番バスの停留所stopsのidとnameを与える

訳者注　停車順で表示

SELECT id, name FROM stops, route
  WHERE id=stop
    AND company='LRT'
    AND num='4'

路線と停留所

このクエリーはロンドン通りLondon Road (149) またはクレイグロックハートCraiglockhart (53)を訪れる路線の数を表示する。クエリーを実行して、これらの停留所を結ぶ２つの路線では停留所のカウントが２個になることに注意する。

HAVING節を追加してそのような路線２つだけを出力するように制限する。

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

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

示してある自己結合を実行してクレイグロックハート(53)から路線乗り換えせずに行けるすべての場所をb.stop(訳者注 左から4列目)が与えることを確認する。

クレイグロックハートからロンドン通り(149)の路線numを表示するようにクエリーを変更する。

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

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

示したクエリは以前のものの類似だが、stopsテーブルのコピーを2つ結合することで番号ではなく名前で停留所を参照できる。

クエリを変更して「Craiglockhart」と「London Road」の間の路線を表示する。これらの場所に飽きたら「フェアマイルヘッド'Fairmilehead'」と「トールクロス'Tollcross'」で試す。

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'

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'

自己結合の利用

ヘイマーケット(停留所115）とリース（停留所137）を結ぶ全ての路線サービスのリストを与える。

SELECT DISTINCT R1.company, R1.num
  FROM route R1, route R2
  WHERE R1.num=R2.num AND R1.company=R2.company
    AND R1.stop=115 AND R2.stop=137

「クレイグロックハート'Craiglockhart'」と「トールクロス'Tollcross'」停留所stopsを結ぶサービスの一覧を提供する。

SELECT R1.company, R1.num
  FROM route R1, route R2, stops S1, stops S2
  WHERE R1.num=R2.num AND R1.company=R2.company
    AND R1.stop=S1.id AND R2.stop=S2.id
    AND S1.name='Craiglockhart'
    AND S2.name='Tollcross'

LRT社が提供するバスで、クレイグロックハート'Craiglockhart'から1回の乗車で到達できる停留所（クレイグロックハート自体を含む）のリスト(distinctで重複無し)を提供する。関連するサービスの会社名とバス番号を含めること。

SELECT DISTINCT S2.name, R2.company, R2.num
FROM stops S1, stops S2, route R1, route R2
WHERE S1.name='Craiglockhart'
  AND S1.id=R1.stop
  AND R1.company=R2.company AND R1.num=R2.num
  AND R2.stop=S2.id

クレイグロックハートCraiglockhartからロチェンドLochendまで、2つのバスを利用するルートを見つける。 最初のバスのバス番号と会社名、乗り換え停留所の名前、2番目のバスのバス番号と会社名を示す。

クレイグロックハートとロチェンドを訪れるバスを見つけるために、2回の自己結合を行い、その後一致する停留所で結合する。

SELECT bus1.num, bus1.company, name, bus2.num, bus2.company FROM (SELECT start1.num, start1.company, stop1.stop FROM route AS start1 JOIN route AS stop1 ON start1.num = stop1.num AND start1.company = stop1.company AND start1.stop != stop1.stop WHERE start1.stop = (SELECT id FROM stops WHERE name = 'Craiglockhart')) AS bus1 JOIN (SELECT start2.num, start2.company, start2.stop FROM route AS start2 JOIN route AS stop2 ON start2.num = stop2.num AND start2.company = stop2.company and start2.stop != stop2.stop WHERE stop2.stop = (SELECT id FROM stops WHERE name = 'Lochend')) AS bus2 ON bus1.stop = bus2.stop JOIN stops ON bus1.stop = stops.id ORDER BY bus1.num, bus1.company, name, bus2.num, bus2.company

Clear your results

Self join Quiz