Self Join

1.

select count(*)
from stops

2.

select id
from stops
where name = 'Craiglockhart'

3.

select id,name
from stops
join route on stops.id=route.stop
where company = 'LRT' and num = '4'
-- 这里直接筛选 num = 4 代码报错，原因是 num 在表中是 str 类型

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 = (
select id from stops where name = 'London Road')
-- 认真读题

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.num = b.num and a.company = b.company)
where a.stop = 115 and b.stop = 137

8.

select a1.company,a1.num
from route as a1
join route as a2 on a1.num = a2.num and a1.company = a2.company
-- 先得出所有经停路线点
join stops as a3 on a1.stop = a3.id
join stops as a4 on a2.stop = a4.id
-- 将所有精听路线点关联名称
where a3.name = 'Craiglockhart' and a4.name = 'Tollcross'

9.

select a4.name,a1.company,a1.num
from route as a1
join route as a2 on a1.num = a2.num and a1.company = a2.company
join stops as a3 on a1.stop = a3.id
join stops as a4 on a2.stop = a4.id
where a3.name = 'Craiglockhart'

10.非常有趣的一道题！

select distinct a.num,a.company,a.name,b.num,b.company
from (
  -- 找出所有 Craiglockhart 为起点，终点为 x 的站点
  select a1.company,a1.num,a3.name as start,a4.name
  from route as a1
  join route as a2 on a1.num = a2.num and a1.company = a2.company
  join stops as a3 on a1.stop = a3.id
  join stops as a4 on a2.stop = a4.id
  where a3.name = 'Craiglockhart' ) as a
join (
  -- 找出所有起点为 x ，终点为 Lochend 的站点
    select b1.company,b1.num,b3.name as start,b4.name
    from route as b1
    join route as b2 on b1.num = b2.num and b1.company = b2.company
    join stops as b3 on b1.stop = b3.id
    join stops as b4 on b2.stop = b4.id
  -- 起点和终点一致
    where b3.name = 'Lochend' ) as b on a.name = b.name