Sqlzoo

SQLZOO：自己結合ノート



Sqlzoo Self Join Notes



自己結合

エジンバラバス
画像
1、データベースにあるストップの数。

SELECT COUNT(*) FROM stops

2、ストップ「クレイグロックハート」のID値を見つけます



SELECT id FROM stops WHERE name = 'Craiglockhart'

3、「4」「LRT」サービスの停車地のIDと名前を入力します。

SELECT id, name FROM route JOIN stops ON (id = stop) WHERE num = 4 AND company = 'LRT' ORDER BY pos

4、表示されているクエリは、ロンドンロード（149）またはクレイグロックハート（53）のいずれかを訪れるルートの数を示しています。クエリを実行し、これらのストップをリンクする2つのサービスのカウントが2であることに注意してください。HAVING句を追加して、出力をこれら2つのルートに制限します。



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

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

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

6、表示されているクエリは前のクエリと似ていますが、ストップテーブルの2つのコピーを結合することで、番号ではなく名前でストップを参照できます。 「Craiglockhart」と「LondonRoad」の間のサービスが表示されるようにクエリを変更します。これらの場所にうんざりしている場合は、「トールクロス」に対して「フェアマイルヘッド」を試してください

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'

7、ストップ115と137を接続するすべてのサービスのリストを提供します（「ヘイマーケット」と「リース」）



SELECT company, num FROM route WHERE num IN (SELECT a.num FROM route AS a, route AS b WHERE a.company = b.company AND a.num = b.num AND a.stop = '115' AND b.stop = '137') GROUP BY num, company

8、「クレイグロックハート」と「トールクロス」の停留所を結ぶサービスのリストを提供します

SELECT b.company, b.num FROM route a JOIN route b ON (a.company = a.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'

9、LRT会社が提供する「クレイグロックハート」自体を含む1つのバスに乗ることで、「クレイグロックハート」から到達できる停車地の明確なリストを提供します。会社とバス番号を含めます。関連するサービスの。

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

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

SELECT v1.r1num, v1.r1com, v1.trans1, v2.r4num, v2.r4com FROM (SELECT s1.name AS begin, r1.num AS r1num, r1.company AS r1com, s2.name AS trans1 FROM route r1 JOIN route r2 ON r1.company = r2.company AND r1.num = r2.num JOIN stops s1 ON s1.id = r1.stop JOIN stops s2 ON s2.id = r2.stop WHERE s1.name = 'Craiglockhart') AS v1 JOIN(SELECT s3.name AS trans2, r4.num AS r4num, r4.company AS r4com, s4.name AS final FROM route r3 JOIN route r4 ON r3.company = r4.company AND r3.num = r4.num JOIN stops s3 ON s3.id = r3.stop JOIN stops s4 ON s4.id = r4.stop WHERE s4.name = 'Lochend') AS v2 ON v1.trans1 = v2.trans2 ORDER BY r1num, trans1, r4num