題目鏈接：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
