SQLZOO英文版答案记录/Self join

1

select count(id) from stops

2

select id from stops
where name='Craiglockhart'

3

select stops.id, stops.name
from stops right join route on (stops.id=route.stop)
where route.company='LRT' and route.num='4'

4

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

select company, num, count(*)
from route
where stop in (149,53)
group by company,num
having count(*)=2

5

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

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

select a.company, a.num
from route a join route b
     on (a.num=b.num and a.company=b.company)
where a.stop=115 and b.stop=137
group by a.company, a.num

select a.company, a.num
from route a join route b on (a.num=b.num and a.company=b.company)
     join stops stopa on (a.stop=stopa.id)
     join stops stopb on (b.stop=stopb.id)
where stopa.name='Haymarket' and stopb.name='Leith'
group by a.company, a.num

8

select a.company,a.num
from route a join route b on (a.num=b.num and a.company=b.company)
     join stops stopa on (stopa.id=a.stop)
     join stops stopb on (stopb.id=b.stop)
where stopa.name='Craiglockhart'
and stopb.name='Tollcross'
group by a.company, a.num

9

select pb.name, a.company, a.num 
from route a join route b on (a.company=b.company and a.num=b.num)
     join stops pa on (pa.id=a.stop)
     join stops pb on (pb.id=b.stop)
where pa.name='Craiglockhart' 

10、右上角SQL Engine选成MySQL时，因为顺序与答案不同，总是显示错误。改成Microsoft SQL就好了。

select C.num, C.company,C.name,L.num, L.company
from 
(SELECT a1.num, a1.company, pb1.name,pb1.id
FROM route a1 JOIN route b1 ON
  (a1.company=b1.company AND a1.num=b1.num)
  JOIN stops pa1 ON (pa1.id=a1.stop)
  JOIN stops pb1 ON (pb1.id=b1.stop)
WHERE pa1.name='Craiglockhart') as C
join
(SELECT a2.num, a2.company, pa2.name
FROM route a2 JOIN route b2 ON
  (a2.company=b2.company AND a2.num=b2.num)
  JOIN stops pa2 ON (pa2.id=a2.stop)
  JOIN stops pb2 ON (pb2.id=b2.stop) 
WHERE pb2.name='Lochend') as L
on (C.name=L.name)