Self join/zh

Edinburgh.png

1
2
1.數據庫中有多少個站stops。
select count(id) from stops
1
2
3
2.找出車站'Craiglockhart'的id
select id from stops
where name = 'Craiglockhart'
1
2
3
3.列出巴士公司'LRT''4'號巴士線的站編號id和站名name
select id,name from route join stops on (id=stop)
where company = 'LRT' and num = '4'
1
2
3
4
5
4.以下查詢列出途經 London Road (149) 或 Craiglockhart (53)的巴士線號碼。注意有兩條路線會經過這兩個站兩次。 加入 HAVING 語句來限制只列出這兩條路線。
SELECT company, num, count(stop)
FROM route WHERE stop=149 OR stop=53
GROUP BY company, num
HAVING count(stop) = 2
1
2
3
4
5
5.執行自我合拼來,留意b.stop代表由Craiglockhart出發不用轉車可前住的地方。 修改它來顯示由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
1
2
3
4
5
6
7
6.此題和上題相似,但是用兩個stops表來自我合拼。這樣我們可以用站名而非站編號。 修改它來顯示由Craiglockhart到 London Road的服務資料。 如你太悶,可試一試由 'Fairmilehead''Tollcross' (系統會當答錯的。正確有3條路線:11,15,315)
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'
1
2
3
4
7.列出連接115 和 137 ('Haymarket''Leith') 的公司名和路線號碼。不要重覆。
select distinct a.company,a.num
from route a join route b on (a.company=b.company and a.num=b.num)
where a.stop = 115 and b.stop = 137
1
2
3
4
5
6
7
8.列出連接車站stops 'Craiglockhart''Tollcross' 的公司名和路線號碼。
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='Craiglockhart' and stopb.name='Tollcross'
1
2
3
4
5
6
7
9.不重覆列出可以由 'Craiglockhart' 乘一程車到達的站stops,包括'Craiglockhart'本身。 列出站名,公司名和路線號碼。
SELECT 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'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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.

根据Hint的方法(先Craiglockhart->transfer,再从transfer->Lochend,最后join on 中转站相等)
select distinct first.num,first.company,name,second.num,second.company from
(select a.num,a.company,b.stop
from route a join route b on (a.num=b.num and a.company=b.company and a.stop!=b.stop)
join stops stopa on (stopa.id=a.stop)
where stopa.name = 'Craiglockhart')as first
join
(select c.num,c.company,c.stop
from route c join route d on (c.num=d.num and c.company=d.company and c.stop!=d.stop)
join stops stopd on (stopd.id=d.stop)
where stopd.name = 'Lochend')as second
on first.stop=second.stop
join stops on (stops.id=first.stop)
order by first.num,name,second.num

直接疯狂join,最后得到的就是'起始点-中转站-目的地'的表
select distinct a.num,a.company,stopb.name,d.num,d.company
from route a join route b on (a.num=b.num and a.company=b.company and a.stop!=b.stop)
join route c on (b.stop=c.stop)
join route d on (c.num=d.num and c.company=d.company and c.stop!=d.stop)
join stops stopa on (stopa.id=a.stop)
join stops stopb on (stopb.id=b.stop)
join stops stopd on (stopd.id=d.stop)
where stopa.name = 'Craiglockhart' and stopd.name = 'Lochend'
order by a.num,name,d.num

Quiz

1.Select the code that would show it is possible to get from Craiglockhart to Haymarket
Answer:

1
2
3
4
5
SELECT DISTINCT a.name, b.name
FROM stops a JOIN route z ON a.id=z.stop
JOIN route y ON y.num = z.num
JOIN stops b ON y.stop=b.id
WHERE a.name='Craiglockhart' AND b.name ='Haymarket'

2.Select the code that shows the stops that are on route.num ‘2A’ which can be reached with one bus from Haymarket?
Answer:

1
2
3
4
5
SELECT S2.id, S2.name, R2.company, R2.num
FROM stops S1, stops S2, route R1, route R2
WHERE S1.name='Haymarket' AND S1.id=R1.stop
AND R1.company=R2.company AND R1.num=R2.num
AND R2.stop=S2.id AND R2.num='2A'

3.Select the code that shows the services available from Tollcross?
Answer:

1
2
3
4
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='Tollcross'

Summary

  • Self JOIN有点像自身做笛卡尔积
  • 本例数据库的route表Self JOIN的难点主要在于是on(num,company)还是on(stop),详情可见Using a self join链接
    on(num,company):
    同公司,同编号车可到达的站情况
    The result is a list of all pairs of stops which share a service.
    on(stop):
    同站里可到达的车情况
    The result is a list of all pairs of services which share a stop.