sqlzoo习题答案--Self join

最新推荐文章于 2024-02-28 11:58:35 发布

codema

最新推荐文章于 2024-02-28 11:58:35 发布

阅读量1k

点赞数

分类专栏： MySql

本文链接：https://blog.csdn.net/codema/article/details/80838805

版权

本文详细介绍了如何使用SQL进行自连接查询，包括从数据库中获取停靠站数量，查找特定停靠站ID，展示特定路线的停靠站，以及查询不同停靠站之间的公交服务。通过示例查询，解释了如何从Craiglockhart到其他地点，如London Road、Tollcross和Sighthill的公交路线，并展示了涉及两辆公交车的换乘路线。

摘要由CSDN通过智能技术生成

Details of the database Looking at the data

stops(id, name)
route(num,company,pos, stop)

stops	route
id	num
name	company
	pos
	stop

1.How many stops are in the database.

select count(id) 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 id,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 ou