SQLZOO SELF JOIN（第10题待更新）

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

1. 

SELECT COUNT(id)
FROM stops

SELECT id
FROM stops
WHERE name='Craiglockhart'

SELECT stops.id, stops.name FROM 
stops JOIN route ON stops.id=route.stop
WHERE route.num='4' AND route.company='LRT'

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

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

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'

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

SELECT a.company, a.num FROM
route a JOIN route b ON (a.num=b.num AND a.company=b.company)
        JOIN stops sa ON (a.stop=sa.id)
        JOIN stops sb ON (b.stop=sb.id)
WHERE sa.name='Craiglockhart' AND sb.name='Tollcross'

SELECT sb.name, a.company, a.num FROM
route a JOIN route b ON (a.num=b.num AND a.company=b.company)
        JOIN stops sa ON (a.stop=sa.id)
        JOIN stops sb ON (b.stop=sb.id)
WHERE sa.name='Craiglockhart' AND a.company='LRT'

--待更新