【SQL】14.SQLZOO练习9--Self join

题目链接：https://sqlzoo.net/wiki/Self_join

题目描述 1

select count(id) from stops;

2

select id from stops
where name='Craiglockhart';

3

select id,name from stops a
join route b on a.id=b.stop
where num='4' and company ='LRT';

4

SELECT company, num, COUNT(*)
FROM route WHERE stop=149 OR stop=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 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

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

select stopa.name, 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 stopb.name = 'Craiglockhart'

10

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 = 'Sighthill'
and stopb.name = stopc.name
order by length(a.num), b.num, stopb.id, length(c.num), d.num