连接查询包含3
种方式:
本文中学习的是自连接self-join
。涉及到的两个表及其相关的字段
stops(id, name)
route(num, company, pos, stop)
This is a list of areas served by buses. The detail does not really include each actual bus stop - just areas within Edinburgh and whole towns near Edinburgh.
A route is the path through town taken by a bus.
所有的题目运行都出问题,提示表是非法,不存在,why?
select count(stops.id)
from stops;
select id
from stops
where name='Craiglockhart';
select id, name
from stops
join route on stops.id=route.stop
where num=4
and company='LRT';
笔记:
select company, num, count(*)
from route
where stop=149 or stop=53
group by company, num
having count(*) = 2; -- 添加语句,过滤结果
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;
下面的部分需要重新做,参考了两个博主:
https://github.com/edsfocci/SQL-SQL_Zoo/blob/master/09_self_join.sql
https://github.com/jisaw/sqlzoo-solutions/blob/master/self-join.sql
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';
列出连接两个站点的全部服务信息
select distinct a.company, a.num
from route a
join route b on (a.num=b.num and a.company=b.company)
join stops stopa on (a.stop=stopa.id)
join stops stopb on (b.stop=stopb.id)
where a.stop=115
and b.stop=137;
select distinct a.company, a.num
from route a
join route b on (a.num=b.num and a.company=b.company)
join stops stopa on (a.stop=stopa.id)
join stops stopb on (b.stop=stopb.id)
where stopa.name='Craiglockhart'
and stopb.name='Tollcross';
select stopa.name, a.company, a.num
from route a
join route b on (a.num=b.numa and a.company=b.company)
join stops stopa on (a.stop=stopa.id)
join stops stopa on (b.stop=stopb.id)
where stopb.name='Craiglockhart';
select stopa.name, stopb.name
from route a
join route b on (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 = 'Sighthill';
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';
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=r2.id
and r2.num='2A';
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=b.stop)
join stops stopb on (b.stop=stopb.id)
where stopa.name='Tollcross';
油管上看到的一个例子,讲解如何通过不同的方式实现自连接。
inner join
,忽略空行left/right/full join
,保留空行;cross join
,保留空行Oracle数据库支持full join,mysql是不支持full join的