SQLZOO习题详解(10)——Self join

墨蓝 2023-01-19 14:50 3阅读 0赞

--------------------

*  本文原创作者：谷哥的小弟
 *  作者博客地址：http://blog.csdn.net/lfdfhl

--------------------

# 数据表 #

stops表如下：  
![在这里插入图片描述][20210413201959638.png]  
![在这里插入图片描述][20210413202202747.png]

route表如下：  
![在这里插入图片描述][20210413202101324.png]  
![在这里插入图片描述][20210413202219464.png]

# 链接地址 #

[https://napier.sqlzoo.net/wiki/Self\_join][https_napier.sqlzoo.net_wiki_Self_join]

# SQL练习 #

01、请查询站点stop的总数量

SELECT COUNT(*) FROM stops

02、查询站点Craiglockhart对应的id编号

SELECT id FROM stops WHERE name='Craiglockhart'

03、查询LRT公司运营的4号线路的所有站点的id和name

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

04、请查询两次经过149号站点或者53号站点的巴士相关信息

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

![在这里插入图片描述][20210414205029966.png]

05、请查询可从53号站点到149号站点的路线及其所属公司

SELECT a.company, a.num, a.stop, b.stop
    FROM route AS a INNER JOIN route AS b ON a.company = b.company 
    WHERE a.stop = 53 AND b.stop = 149 AND a.num = b.num

![在这里插入图片描述][20210414192025152.png]  
06、请查询可从Craiglockhart到London Road的路线及其所属公司

SELECT a.company, a.num, stopa.name, stopb.name
    FROM route AS a JOIN route AS b ON a.company = b.company
    INNER JOIN stops stopa ON a.stop = stopa.id INNER JOIN stops stopb ON b.stop = stopb.id
    WHERE stopa.name = 'Craiglockhart' AND stopb.name = 'London Road' AND a.num = b.num

![在这里插入图片描述][2021041419233329.png]  
07、请查询可从115号站点Haymarket到137号站点Leith的路线及其所属公司

SELECT DISTINCT a.company, a.num
    FROM route AS a INNER JOIN route AS b ON a.num = b.num
    AND a.company = b.company AND a.stop = 115 AND b.stop = 137

SELECT DISTINCT a.company, a.num
    FROM route AS a INNER JOIN route AS b ON a.company = b.company AND a.num = b.num
    INNER JOIN stops stopa ON a.stop = stopa.id
    INNER JOIN stops stopb ON b.stop = stopb.id
    WHERE stopa.name = 'Haymarket' AND stopb.name = 'Leith'

![在这里插入图片描述][20210414192929604.png]  
08、请查询可从Craiglockhart到Tollcross的路线及其所属公司

SELECT DISTINCT a.company, a.num
    FROM route AS a INNER JOIN route AS b ON (a.company=b.company AND a.num=b.num)
    INNER JOIN stops stopa ON (a.stop=stopa.id)
    INNER JOIN stops stopb ON (b.stop=stopb.id)
    WHERE stopa.name='Craiglockhart' AND stopb.name = 'Tollcross'

![在这里插入图片描述][20210414194955624.png]  
09、请查询在Craiglockhart站点乘坐LRT公司的公交车能到达的其他站点

SELECT DISTINCT stopb.name, a.company, a.num
    FROM route AS a INNER JOIN route AS b ON (a.company=b.company AND a.num=b.num)
    INNER JOIN stops stopa ON (a.stop=stopa.id)
    INNER JOIN stops stopb ON (b.stop=stopb.id)
    WHERE stopa.name='Craiglockhart' AND a.company = 'LRT'

![在这里插入图片描述][20210414195332327.png]  
10、查询从Craiglockhart 到Lochend，并且包含2路公交的线路(即只中转一次)

SELECT m.num, m.company, name, n.num, n.company
    FROM (
    	SELECT a.num, a.company, b.stop
    	FROM route a
    		JOIN route b
    		ON a.company = b.company
    			AND a.num = b.num
    			AND a.stop != b.stop
    	WHERE a.stop = (
    		SELECT id
    		FROM stops
    		WHERE name = 'Craiglockhart'
    	)
    ) m
    	JOIN (
    		SELECT d.num, d.company, c.stop
    		FROM route c
    			JOIN route d
    			ON c.company = d.company
    				AND c.num = d.num
    				AND c.stop != d.stop
    		WHERE d.stop = (
    			SELECT id
    			FROM stops
    			WHERE name = 'Lochend'
    		)
    	) n
    	ON m.stop = n.stop
    	JOIN stops ON m.stop = stops.id
    WHERE m.num != n.num
    ORDER BY m.num, stops.name, n.num;

![在这里插入图片描述][20210414204311544.png]

[20210413201959638.png]: /images/20221021/afcd40070b2e4f109490fb7248695260.png
[20210413202202747.png]: /images/20221021/038d07c934d346c0b5105534821b7703.png
[20210413202101324.png]: /images/20221021/c799b9e6e9324561a1aa8d5909b170f3.png
[20210413202219464.png]: /images/20221021/024e92cd47f9419282df3c9e119ea261.png
[https_napier.sqlzoo.net_wiki_Self_join]: https://napier.sqlzoo.net/wiki/Self_join
[20210414205029966.png]: /images/20221021/ad86b211057a4565b1b1021a39c63b86.png
[20210414192025152.png]: /images/20221021/40ba42a64ed8442782b1e948f6aadb21.png
[2021041419233329.png]: /images/20221021/b6d9a3b1f80e4406b8d8b4c2b8db85ee.png
[20210414192929604.png]: /images/20221021/1d2262f16691431a94e67f770cb3798e.png
[20210414194955624.png]: /images/20221021/6b09bad9c260429d89367a9a24fb39bf.png
[20210414195332327.png]: /images/20221021/12f194699172453fb942e7940c1ec756.png
[20210414204311544.png]: /images/20221021/0baa0c945cf84d4784aaac8bc923fabc.png