--Self join/zh


 --1
select count(id)
from stops;


 --2
select id
from stops
where name = 'Craiglockhart';


 --3
select 
stops.id 
,stops.name 
from  
where route.company = 'LRT'
and route.num = 4;


 --4
select 
company 
,num 
,count(*) 
from route 
where stop in ('149','53')
group by num,company
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 
,c.name  
,d.name 
from route a join route b on (a.company = b.company 
                              and a.num = b.num)
             join stops c on (c.id = a.stop)
             join stops d on (d.id = b.stop)
where a.stop = '53'
  and b.stop = '149';


 --7
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';


 --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 c on (c.id = a.stop)
             join stops d on (d.id = b.stop)
where c.name = 'Craiglockhart' 
  and d.name = 'Tollcross';


 --9
select 
distinct stops.name 
,route.company 
,route.num
from stops join route on stops.id = route.stop
where route.num in (select route.num
       from stops join route on stops.id = route.stop
       where stops.name =  'Craiglockhart')
      and route.company = 'LRT'
order by route.num;
/*结果与答案数据一致，但报错。下方为另一种解法*/
select 
distinct d.name 
,a.company
,a.num 
from route a join route b on (a.company = b.company 
                          and a.num = b.num)
             join stops c on c.id = a.stop
             join stops d on d.id = b.stop
where c.name = 'Craiglockhart' 
  and a.company = 'LRT';


 --10
select 
distinct x.num
,x.company
,name
,y.num
,y.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
     where a.stop = (select id 
                     from stops 
                     where name ='Craiglockhart')) as x
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
      where d.stop =(select id 
                     from stops 
                     where name = 'Lochend'))as y
on x.stop = y.stop
join stops on x.stop = stops.id
order by x.num,stops.name,y.num;
/*看报错摸索了很长时间才排对顺序*/