sqlzoo--self join

• 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

sqlzoo–self join

已有字段：

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

1. 数据库中有多少个站点

   select count(id) from stops;
   

2. 找出车站‘craiglockhart’的id

   select id from stops
   where name = 'Craiglockhart';
   

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

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

4. 查询途经London Road (149) 或 Craiglockhart (53)的巴士路线号码，有两条路线会经过这个站点两次，使用having语句列出这两条路线。

   SELECT company, num, COUNT(*)
   FROM route WHERE stop=149 OR stop=53
   GROUP BY company,num
   HAVING count(*) = 2;
   

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

   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. 使用两个stops表来进行自连接，来显示Craiglockhart到 London Road的服务资料。

   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. 列出连接115和137公司名称和路线号码，不要重复

   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;
   

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

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

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

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

10. 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.

    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

1. 显示从Craiglockhart 到Haymarket的可能路线

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

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

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

3. 到tollcross的可能服务路线

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