プログラミング

9. Self JOIN /SQL ZOO の答えと解説

sql_zoo_9 self join

SQLZooの答えと解説です。

今までの解説はこちら

4. SELECT within SELECT/SQL ZOO の答えと解説
5. SUM and COUNT/SQL Zooの答えと解説
6. JOIN /SQL ZOOの答えと解説
7. More JOIN operations /SQL ZOOの答えと解説
8. Using NUll/SQL Zooの答えと解説

1. How many stops are in the database.

  Q. stopsはいくつ有りますか?

SELECT COUNT(*) FROM stops

2. Find the id value for the stop ‘Craiglockhart’

  Q. ストップ「Craiglockhart」のID値を 見つけます

SELECT id FROM stops
WHERE name = 'Craiglockhart'

3. Give the id and the name for the stops on the ‘4’ ‘LRT’ service.

  Q. 「4」「LRT」サービスの停車地のIDと名前を指定します。

SELECT id,name FROM stops s
JOIN route r ON r.stop = s.id
WHERE r.num = '4'

Google翻訳ボロボロですね。 ニュアンスで解いてください 笑

4. Routes and stops

  Q. The query shown gives the number of routes that visit either London Road (149) or Craiglockhart (53).

Run the query and notice the two services that link these stops have a count of 2. Add a HAVING clause to restrict the output to these two routes.

表示されているクエリは、ロンドンロード(149)またはクレイグロックハート(53)を訪れるルートの数を示しています。

クエリを実行して、これらのストップをリンクする2つのサービスのカウントが2であることに注意してください。

HAVING句を追加して、出力をこれら2つのルートに制限します。 和訳わかりづらすぎるけど、countは2ジャストじゃないとあかんのね

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

5. Execute the self join shown and observe that b.stop gives all the places you can get to from Craiglockhart, without changing routes.

  Change the query so that it shows the services from Craiglockhart to London Road.

表示された自己結合を実行し、b.stopがルートを変更せずにCraiglockhartから到達できるすべての場所を提供することを確認します。

クエリを変更して、CraiglockhartからLondon Roadまでのサービスを表示します。

#London Roadの番号は
SELECT * FROM stops
WHERE name = 'London Road'
# 上記を利用して、Craiglockhart かつ London Roadに止まるルートは
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. The query shown is similar to the previous one, however by joining two copies of the stops table we can refer to stops by name rather than by number.

 

Change the query so that the services between ‘Craiglockhart’ and ‘London Road’ are shown.
If you are tired of these places try ‘Fairmilehead’ against ‘Tollcross’

示されたクエリは、しかし、の2つのコピー接合することで、以前のものと似て停止したテーブルを、我々はを参照することができます停止によって名前ではなく番号で。

「Craiglockhart」と「London Road」の間のサービスが表示されるようにクエリを変更します。

これらの場所に飽きたら、「トールクロス」に対して「フェアマイルヘッド」を試してください。

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. Give a list of all the services which connect stops 115 and 137 (‘Haymarket’ and ‘Leith’)

  Q. 115と137の停留所を結ぶすべてのサービスのリストを提供します(「ヘイマーケット」と「リース」)

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'

8. Give a list of the services which connect the stops ‘Craiglockhart’ and ‘Tollcross’

  Q. 停留所「クレイグロックハート」と「トールクロス」 を結ぶサービスのリストを提供する

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'

9. 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.

Q. LRT会社が提供する「Craiglockhart」自体を含む1つのバスを利用して、「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'
AND a.company = 'LRT';

10. 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.

Q. ‘Craiglockhart’から’LOCHEND’へ行くことができる2台のバスを含むルート検索 バス番号、最初のバスの会社、乗り換えの停車地の名前、バス番号 と2番目のバスの会社を表示します。

注意点が2つ。 1つはSELECT文が長すぎると、下記のようなErrorが出るためSET SQL_BIG_SELECTS=1;を追記すること

The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

2つめが下記のコードで出力される結果は、答えと同じだが、 出力される順序が異なるため。間違った回答扱いになる。 うまく並び替えることができなかったので、うまくいくやり方あれば教えてください。

SET SQL_BIG_SELECTS=1;

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 stopd.name='Lochend'
AND stopb.name = stopc.name

 

https://marucoblog.com/programing/8-using-null-sql-zoo/

COMMENT

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です