SQLZOO SELF JOIN 参考答案（含表格样式和解题思路）

时间：2024-10-14 07:58:40

1.
首先放上数据源信息，然后需要了解这个数据库的构成，在ROUTE中，一个NUM 和COMOANY构成了一个主键。这就说明一个后面的POS 和 STOP 会有重复（因为可能有2个公司的公交车走两趟非常相似的公司，或者一个公司内2趟公有几站是重合的），这个也是运用自连接的主要场合（有一对多的映射）

题目如下：
many stops are in the database.

这里为防止计算重复id, 加了DISTINCT

SELECT DISTINCT count(id)
FROM stops
  • 1
  • 2

the id value for the stop ‘Craiglockhart’

SELECT id
FROM stops
WHERE name LIKE "Craiglockhart"
  • 1
  • 2
  • 3

the id and the name for the stops on the ‘4’ ‘LRT’ service.

这里需要注意的是‘4’ 和 4
根据题意，应该是带单引号的4，当我直接输入4的时候，MYSQL自动排序了。可能由于数据类型不同操作不同吧。

#子查询
SELECT id,name
FROM stops
WHERE id IN 
(SELECT stop
FROM route
WHERE num='4'
AND company LIKE 'LRT'
)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
#或者JOIN
SELECT id,name 
FROM stops s join route r
ON s.id=r.stop
WHERE r.num='4'AND r.company LIKE 'LRT'
  • 1
  • 2
  • 3
  • 4
  • 5
  1. The query shown gives the number of routes that visit either London Road (149) or Craiglockhart (53). Run the query and notice the two services that link these stops have a count of 2. Add a HAVING clause to restrict the output to these two routes.

number of routes: 运用count函数

SELECT company, num, COUNT(*)
FROM route 
WHERE stop=149 OR stop=53
GROUP BY company, num
Having COUNT(*)=2
  • 1
  • 2
  • 3
  • 4
  • 5
  1. Execute the self join shown and observe that gives all the places you can get to from Craiglockhart, without changing routes. Change the query so that it shows the services from Craiglockhart to London Road.

思路：一开始不理解的是FROM TO这个意思怎么用编程实现，后来参考了一下他的答案，发现一个利用JOIN 自我结合之后就可以实现笛卡尔积一样的表格（一对多的映射）
在这里插入图片描述这样只需要用where 进行过滤查出同时具有这2个站点的num 和 company就可以了

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
  • 1
  • 2
  • 3
  • 4
  1. The query shown is similar to the previous one, however by joining two copies of the stops table we can refer to stops by name rather than by number. Change the query so that the services between ‘Craiglockhart’ and ‘London Road’ are shown. If you are tired of these places try ‘Fairmilehead’ against ‘Tollcross’

思路： 这个是从上面一个题目引申而来，主要的不同就是需要通过stop=id,再用JOIN连接另外一张表，以显示name.

第一种是ZOO提供的答案，有一点需要注意（因为我错了TT） 就是WHERE子句要放在最后全部串联好表之后进行过滤。

否则就是我采用的第二种方法，先筛选好你需要的行，在连接外部表。

#这是SQLZOO给的参考答案：
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'


#这是先WHERE过滤，再连接stops表：
SELECT tmp.company,tmp.num,sa.name,sb.name
FROM
(SELECT ra.company,ra.num,ra.stop as stop1,rb.stop as stop2
FROM route ra
JOIN route rb 
ON ra.num=rb.num AND ra.company=rb.company
WHERE ra.stop=53 AND rb.stop=149) tmp
JOIN stops sa
ON sa.id=tmp.stop1
JOIN stops sb
ON sb.id=tmp.stop2
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  1. Give a list of all the services which connect stops 115 and 137 (‘Haymarket’ and ‘Leith’)

思路：这个之前有一道题和他非常相似，唯一需要注意的就是，由于一个公司一个NUM可能会有2条线同时经过这里，所以需要去重（DISTINCT）

SELECT DISTINCT ra.company,ra.num
FROM route ra 
JOIN route rb
ON ra.num=rb.num AND ra.company=rb.company
WHERE ra.stop=115 AND rb.stop=137
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  1. Give a list of the services which connect the stops ‘Craiglockhart’ and ‘Tollcross’

思路： 这个和上面第六题很像，就不赘述了

SELECT ra.company,ra.num
FROM route ra 
JOIN route rb
ON ra.num=rb.num AND ra.company=rb.company
JOIN stops sa
ON sa.id=ra.stop
JOIN stops sb
ON sb.id=rb.stop
WHERE sa.name='Craiglockhart' AND sb.name= 'Tollcross'
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  1. Give a distinct list of the stops which may be reached from ‘Craiglockhart’ by taking one bus, including ‘Craiglockhart’ itself, offered by the LRT company. Include the company and bus no. of the relevant services.

思路：也是先连接好需要的总表，完成一对多的映射之后，用WHERE筛选子句找到满足需要的2个条件。

SELECT distinct sb.name,ra.company,ra.num
FROM route ra JOIN route rb
ON ra.num=rb.num AND ra.company=rb.company
JOIN stops sa ON sa.id=ra.stop
JOIN stops sb ON sb.id=rb.stop
WHERE sa.name= 'Craiglockhart' 
AND ra.company='LRT'
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  1. Find the routes involving two buses that can go from Craiglockhart to Lochend.
    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.

思路：这道题想了半天，他的难点或者说考点就是你需要将ROUTE表起始点为“Craiglockhart ”的所有和他连接的站点找到（非常像第5题，但是终点并不是LONDON LAND罢了），这个就需要用JOIN（进行自连接创造以一映射到多的表格）。终点为Lochend 同理。

最后用JOIN 找到这2个表中，表一（BUS1）的终点和表二（BUS2）的起点相同的行。再拼接到STOPS表上已找到NAME输出

这道题我是参考下面这篇博文，把他的思想理解写出来的。
（/sinat_41944023/article/details/95447119?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-16&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-16）

SELECT DISTINCT bus1.num,bus1.company,name,bus2.num,bus2.company
FROM
(SELECT ra.num,ra.company,rb.stop
FROM route ra 
JOIN route rb
ON ra.num=rb.num 
AND ra.company=rb.company
AND ra.stop!=rb.stop
WHERE ra.stop = (SELECT id FROM stops WHERE name='Craiglockhart') )bus1

JOIN

(SELECT ra.num,ra.company,ra.stop
FROM route ra 
JOIN route rb
ON ra.num=rb.num 
AND ra.company=rb.company
AND ra.stop!=rb.stop
WHERE rb.stop = (SELECT id FROM stops WHERE name='Lochend') )bus2
ON bus1.stop=bus2.stop
JOIN stops st
ON bus1.stop=st.id
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22

相关文章