sqlzoo–select basics,select from world
sqlzoo–select from nobel ,select in select
sqlzoo–sum and count
sqlzoo–the join operation
sqlzoo–More JOIN operations
sqlzoo–using null
sqlzoo–self join

sqlzooself_join_9">sqlzoo–self join

已有字段：

table:stops	table:route
id	num
name	company
	pos
	stop

数据库中有多少个站点
```
sql">select count(id) from stops;
```

找出车站‘craiglockhart’的id

sql">select id from stops
where name = 'Craiglockhart';

列出巴士公司’LRT‘4号线的站点编号和站名

sql">select id,name from route 
join stops on (id = stop)
where company = 'LRT' and num = 4;

查询途经London Road (149) 或 Craiglockhart (53)的巴士路线号码，有两条路线会经过这个站点两次，使用having语句列出这两条路线。
```
sql">SELECT company, num, COUNT(*)
FROM route WHERE stop=149 OR stop=53
GROUP BY company,num
HAVING count(*) = 2;
```

使用自连接来显示能够从Craiglockhart到 London Road直达的路线

sql">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;

使用两个stops表来进行自连接，来显示Craiglockhart到 London Road的服务资料。

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

列出连接115和137公司名称和路线号码，不要重复

sql">select company,num from route where num in 
(select  a.num from route as a,route as b 
where a.stop != b.stop 
and (a.company = b.company and a.num=b.num)
and a.stop='115' and b.stop='137')
group by num,company;

列出连接车站stops’Craiglockhart’ 到 ‘Tollcross’ 的公司名和路线号码

sql">SELECT b.company, b.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';

不重复列出由 ‘Craiglockhart’ 乘一站到达的站点，包括其本身，列出站名，公司名以及路线号码

sql">select  stopb.name,a.company,a.num
from route as a join route as b 
on (a.company=b.company and a.num=b.num)
join stops as stopa on (stopa.id=a.stop)
join stops as stopb on (stopb.id=b.stop)
where stopa.name='Craiglockhart';

Find the routes involving two buses that can go from Craiglockhart to Sighthill.
Show the bus no. and company for the first bus, the name of the stop for the transfer,
and the bus no. and company for the second bus.

sql">SELECT DISTINCT bus1.num, bus1.company, name, bus2.num, bus2.company 
FROM(SELECT start1.num, start1.company, stop1.stop 
FROM route AS start1 
JOIN route AS stop1 
ON start1.num = stop1.num AND start1.company = stop1.company AND start1.stop != stop1.stop 
WHERE start1.stop = (SELECT id FROM stops WHERE name = 'Craiglockhart')) AS bus1 
JOIN (SELECT start2.num, start2.company, start2.stop 
FROM route AS start2 
JOIN route AS stop2 ON start2.num = stop2.num AND start2.company = stop2.company and start2.stop != stop2.stop 
WHERE stop2.stop = (SELECT id FROM stops WHERE name = 'Sighthill')) AS bus2 ON bus1.stop = bus2.stop 
JOIN stops ON bus1.stop = stops.id;

self join quiz

显示从Craiglockhart 到Haymarket的可能路线

sql">SELECT DISTINCT a.name, b.name
  FROM stops a JOIN route z ON a.id=z.stop
  JOIN route y ON y.num = z.num
  JOIN stops b ON y.stop=b.id
 WHERE a.name='Craiglockhart' AND b.name ='Haymarket';

在路线2A上并且能够一站到达haymarket的站点

sql">SELECT S2.id, S2.name, R2.company, R2.num
  FROM stops S1, stops S2, route R1, route R2
 WHERE S1.name='Haymarket' AND S1.id=R1.stop
   AND R1.company=R2.company AND R1.num=R2.num
   AND R2.stop=S2.id AND R2.num='2A';

到tollcross的可能服务路线

sql">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='Tollcross';