mysql self join_159、MySQL入门（九）：Self join用法

Sqlzoo习题练习：Self join用法

此数据库由两个表组成： stops和routes 存储着爱丁堡公共汽车的信息。

两个表的列表头如下：

1.stops 和route的表头.png

2.stops and route.png

下面为Self join 习题内容：

--#1

/*

How many stops are in the database.

*/

SELECT COUNT(*)

FROM stops;

--#2

/*

Find the id value for the stop 'Craiglockhart'

*/

SELECT id

FROM stops

WHERE name = 'Craiglockhart' ;

--#3

/*

Give the id and the name for the stops on the '4' 'LRT' service.

*/

SELECT stops.id,stops.name

FROM stops JOIN route

ON (stops.id = route.stop)

WHERE num = 4

AND company = 'LRT';

--#4

/*

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.

*/

SELECT company, num, COUNT(*)

FROM route WHERE stop=149 OR stop=53

GROUP BY company, num

HAVING COUNT(*) = 2;

知识点：self join自连接

self join是自己内部连接,自连接，可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。有点像LEFT JOIN。

inner join 在和自己连接时，虽然效果是一样的，但是在处理过程上，仍然是被当成了两张表，只不过这两张表是名称、结构都一样的表。在使用时，这样的连接是进行了笛卡尔集的连接，笛卡尔集进行的是自然连接，要去除重复的行。

--#5

/*

Execute the self join shown and observe that b.stop 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.

*/

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

/*

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'

*/

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

/*

Give a list of all the services which connect stops 115 and 137 ('Haymarket' and 'Leith')

*/

SELECT DISTINCT a.company,b.num

FROM route AS a

JOIN route AS b

ON (a.company,a.num) = (b.company,b.num)

WHERE a.stop = 115

AND b.stop = 137;

--#8

/*

Give a list of the services which connect the stops 'Craiglockhart' and 'Tollcross'

*/

SELECT a.company, b.num

FROM route AS a

JOIN route AS b

ON (a.company, a.num) = (b.company, b.num)

JOIN stops AS s1

ON a.stop = s1.id

JOIN stops AS s2

ON b.stop = s2.id

WHERE s1.name = 'Craiglockhart'

AND s2.name = 'Tollcross'

AND s2.name = 'Tollcross';

--#9

/*

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.

*/

SELECT s2.name,r1.company,r1.num

FROM route AS r1

JOIN route AS r2

ON (r1.company,r1.num) = (r2.company,r2.num)

JOIN stops AS s1

ON r1.stop = s1.id

JOIN stops AS s2

ON r2.stop = s2.id

WHERE s1.name = 'Craiglockhart' ;