http://old.sqlzoo.net/wiki/SQL_Tutorial/zhold.sqlzoo.net
一、SELECT basics
1、顯示德國 Germany 的人口。
select population
from world
WHERE name = 'Germany';
2、查詢面積為 5,000,000 以上平方公里的國家,對每個國家顯示她的名字和人均國內生產總值(gdp/population)。
select name,gdp/population
from world
where area>5000000;
3、顯示“Ireland 愛爾蘭”,“Iceland 冰島”,“Denmark 丹麥”的國家名稱和人口。
select name,population
from world
where name in ('Ireland','Iceland','Denmark');
4、顯示面積為 200,000 及 250,000 之間的國家名稱和該國面積。
select name,area
from world
where area between 200000 and 250000；
使用between的注意点：
①.存储的数据是date类型，则同时包含首尾；
②.存储的数据是datetime类型，则只包含头，不包含尾；
③.存储的数据是数字类型，则同时包含首尾。
5、找出以 Y 為開首的國家。
select name
from world
where name like 'Y%';
%表示通配符；_只匹配一个字符
6、找出以 Y 為結尾的國家。
select name
from world
where name like '%Y';
7、找出所有國家,其名字包括字母x。
select name
from world
where name like '%x%';
8、找出所有國家,其名字以 land 作結尾。
select name
from world
where name like '%land';
9、找出所有國家,其名字以 C 作開始,ia 作結尾。
select name
from world
where name like 'C%ia';
10、找出所有國家,其名字包括字母oo。
select name
from world
where name like '%oo%';
11、找出所有國家,其名字包括三個或以上的a。
select name
from world
where name like '%a%a%a%';
12、找出所有國家,其名字以t作第二個字母。
select name
from world
where name like '_t%';
13、找出所有國家,其名字都有兩個字母 o,被另外兩個字母相隔着。
select name
from world
where name like '%o__o%';
14、找出所有國家,其名字都是 4 個字母的。
select name
from world
where name like '____';
15、顯示所有國家名字,其首都和國家名字是相同的。
select name
from world
where name=capital;
16、顯示所有國家名字,其首都是國家名字加上”City”。
select name
from world
where capital=concat(name,' city');
17、找出所有首都和其國家名字,而首都要有國家名字中出現。
select capital,name
from world
where capital like concat('%',name,'%');
18、找出所有首都和其國家名字,而首都是國家名字的延伸。
select name,capital
from world
where capital like concat('%',name,'_%');
19、顯示國家名字，及其延伸詞，如首都是國家名字的延伸
select name, replace(capital, name, '')
from world
where capital like concat('%',name,'%')
and replace(capital, name, '') <> '';
replace(A,B,C)替换函数把字段A中的B字符串换成C字符串
二、SELECT from world
1、觀察運行一個簡單的SQL命令的結果。
SELECT name, continent, population
FROM world;
2、 顯示具有至少2億人口的國家名稱。
select name
from world
where population>=200000000;
3、找出有至少200百萬(2億)人口的國家名稱，及人均國內生產總值。
select name,gdp/population
from world
where population>=200000000;
4、顯示'South America'南美洲大陸的國家名字和以百萬為單位人口數。 將人口population 除以一百萬(1000000)得可得到以百萬為單位人口數。
select name,population/1000000
from world
where continent='South America';
5、顯示法國，德國，意大利(France, Germany, Italy)的國家名稱和人口。
select name,population
from world
where name in ('France','Germany','Italy');
6、顯示包含單詞“United”為名稱的國家。
select name
from world
where name like '%United%';
7、成為大國的兩種方式：如果它有3百萬平方公里以上的面積，或擁有250百萬(2.5億)以上人口。 展示大國的名稱，人口和面積。
select name,population,area
from world
where area>3000000 or population>250000000;
8、顯示以人口或面積為大國的國家，但不能同時兩者。顯示國家名稱，人口和面積。
select name,population,area
from world
where (area>3000000 and population<=250000000) or (area<=3000000 and population>250000000);
9、對於南美顯示以百萬計人口，以十億計2位小數GDP。
select name,round(population/1000000,2),round(GDP/1000000000,2)
from world
where continent='South America';
round(A,N)函数保留A的N位小数
10、顯示萬億元國家的人均國內生產總值，四捨五入到最近的$ 1000。
select name,round(GDP/population/1000,0)*1000
from world
where GDP>=1000000000000;
11、Show the name - but substitute Australasia for Oceania - for countries beginning with N.
select name,
case when continent='Oceania' then 'Australasia'
else continent end
from world
where name like 'N%';
12、Show the name and the continent - but substitute Eurasia for Europe and Asia; substitute America - for each country in North America or South America or Caribbean. Show countries beginning with A or B
select name,
case when continent in ('Europe','Asia') then 'Eurasia'
when continent in ('North America','South America','Caribbean') then 'America'
else continent end
from world
where (name like 'A%') or (name like 'B%');
13、Put the continents right...Oceania becomes Australasia
Countries in Eurasia and Turkey go to Europe/Asia
Caribbean islands starting with 'B' go to North America, other Caribbean islands go to South America
Show the name, the original continent and the new continent of all countries.
select name,continent,
case when continent='Oceania' then 'Australasia'
when continent in ('Eurasia', 'Turkey') then 'Europe/Asia'
when continent='Caribbean' and name like 'B%' then 'North America'
when continent='Caribbean' and name not like 'B%' then 'South America'
else continent end
from world
order by name;
三、SELECT from Nobel
1、更改查詢以顯示1950年諾貝爾獎的獎項資料。
select yr, subject, winner
from nobel
where yr = 1950;
2、顯示誰贏得了1962年文學獎(Literature)。
select winner
from nobel
where yr=1962 and subject='Literature';
3、顯示“愛因斯坦”('Albert Einstein') 的獲獎年份和獎項。
select yr,subject
from nobel
where winner='Albert Einstein';
4、顯示2000年及以後的和平獎(‘Peace’)得獎者。
select winner
from nobel
where yr>=2000 and subject='Peace';
5、顯示1980年至1989年(包含首尾)的文學獎(Literature)獲獎者所有細節(年，主題，獲獎者)。
select yr,subject,winner
from nobel
where (yr between 1980 and 1989) and subject='Literature';
6、顯示總統獲勝者的所有細節：西奧多•羅斯福 Theodore Roosevelt
伍德羅•威爾遜 Woodrow Wilson
吉米•卡特 Jimmy Carter
select *
from nobel
where winner in ('Theodore Roosevelt','Woodrow Wilson','Jimmy Carter');
7、顯示名字為John 的得獎者。 (注意:外國人名字(First name)在前，姓氏(Last name)在後)
select winner
from nobel
where winner like 'John%';
8、顯示1980年物理學(physics)獲獎者，及1984年化學獎(chemistry)獲得者。
select *
from nobel
where ((yr=1980) and (subject='physics'))
or ((yr=1984) and (subject='chemistry'));
9、查看1980年獲獎者，但不包括化學獎(Chemistry)和醫學獎(Medicine)。
select *
from nobel
where yr=1980 and subject not in ('chemistry','medicine');
10、顯示早期的醫學獎(Medicine)得獎者(1910之前，不包括1910)，及近年文學獎(Literature)得獎者(2004年以後，包括2004年)。
select *
from nobel
where (yr<1910 and subject='medicine')
or (yr>=2004 and subject='Literature');
11、Find all details of the prize won by PETER GRÜNBERG
select *
from nobel
where winner='PETER GRÜNBERG';
12、查找尤金•奧尼爾EUGENE O'NEILL得獎的所有細節 Find all details of the prize won by EUGENE O'NEILL
select *
from nobel
where winner='EUGENE O\'NEILL';
13、列出爵士的獲獎者、年份、獎頁(爵士的名字以Sir開始)。先顯示最新獲獎者，然後同年再按名稱順序排列。
select winner,yr,subject
from nobel
where winner like 'Sir%'
order by yr desc,winner asc;
14、Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.
select winner,subject
from nobel
where yr=1984
order by (subject in ('Chemistry','Physics')) asc,subject,winner;
四、SELECT in SELECT
1、列出每個國家的名字 name，當中人口 population 是高於俄羅斯'Russia'的人口。
select name
from world
where population>(select population
from world
where name='Russia');
2、列出歐州每國家的人均GDP，當中人均GDP要高於英國'United Kingdom'的數值。
select name
from world
where continent='Europe'
and gdp/population>(select gdp/population
from world
where name='United Kingdom');
3、在阿根廷Argentina 及 澳大利亞 Australia所在的洲份中，列出當中的國家名字 name 及洲分 continent 。按國字名字順序排序
select name,continent
from world
where continent in (select continent
from world
where name in ('Argentina','Australia'))
order by name asc;
4、哪一個國家的人口比加拿大Canada的多，但比波蘭Poland的少?列出國家名字name和人口population 。
select name,population
from world
where population>(select population
from world
where name='Canada')
and population
from world
where name='Poland');
5、顯示歐洲的國家名稱name和每個國家的人口population。以德國的人口的百分比作人口顯示。
select name,concat(round(population/(select population
from world
where name='Germany')*100,0),'%')
from world
where continent='Europe';
6、哪些國家的GDP比Europe歐洲的全部國家都要高呢? [只需列出 name 。] (有些國家的記錄中，GDP是NULL，沒有填入資料的。)
select name
from world
where gdp> all(select gdp
from world
where continent='Europe' and gdp>0);
select name
from world
where gdp> select max(gdp)
from world
where continent='Europe';
在使用all函数时，需要加入gdp>0的条件，因为有些国家的记录中gdp是null
7、在每一個州中找出最大面積的國家，列出洲份 continent, 國家名字 name 及面積 area。 (有些國家的記錄中，AREA是NULL，沒有填入資料的。)
select w1.continent,w1.name,w1.area
from world w1
where w1.area>=all(select w2.area
from world w2
where w2.continent=w1.continent and area>0);
8、列出洲份名稱，和每個洲份中國家名字按子母順序是排首位的國家名。(即每洲只有列一國)
select w1.continent,w1.name
from world w1
where w1.name<=all(select name
from world w2
where w2.continent=w1.continent);
select continent,min(name)
from world
group by continent;
9、找出洲份，當中全部國家都有少於或等於 25000000 人口. 在這些洲份中，列出國家名字name，continent 洲份和population人口。
select name,continent,population
from world w1
where 25000000>=all(select w2.population
from world w2
where w2.continent=w1.continent);
10、有些國家的人口是同洲份的所有其他國的3倍或以上。列出 國家名字name 和 洲份 continent。
select w1.name,w1.continent
from world w1
where population>=all(select 3*w2.population
from world w2
where w2.continent=w1.continent and w2.name<>w1.name);
五、SUM and COUNT
1、展示世界的總人口。
select sum(population)
from world;
2、列出所有的洲份, 每個只有一次。
select distinct continent
from world;
3、找出非洲(Africa)的GDP總和。
select sum(gdp)
from world
where continent='Africa';
4、有多少個國家具有至少百萬(1000000)的面積。
select count(name)
from world
where area>=1000000;
5、('France','Germany','Spain')(“法國”，“德國”，“西班牙”)的總人口是多少？
select sum(population)
from world
where name in ('France','Germany','Spain');
6、對於每一個洲份，顯示洲份和國家的數量。
select continent,count(name)
from world
group by continent;
7、對於每一個洲份，顯示洲份和至少有1000萬人(10,000,000)口國家的數目。
select continent,count(name)
from world
where population>=10000000
group by continent;
8、列出有至少100百萬(1億)(100,000,000)人口的洲份。
select continent
from world
group by continent
having sum(population)>=100000000;
五、The nobel table can be used to practice more SUM and COUNT functions.
1、找出總共有多少個獎頒發了。
select count(subject)
from nobel;
2、列出每一個獎項(subject), 只列一次
select distinct subject
from nobel;
3、找出物理獎的總頒發次數。
select count(yr)
from nobel
where subject='Physics';
4、對每一個獎項(Subject),列出頒發數目。
select subject,count(yr)
from nobel
group by subject;
5、對每一個獎項(Subject),列出首次頒發的年份。
select subject,min(yr)
from nobel
group by subject;
6、對每一個獎項(Subject),列出2000年頒發的數目。
select subject,count(winner)
from nobel
where yr=2000
group by subject;
7、對每一個獎項(Subject),列出有多少個不同的得獎者。
select subject,count(distinct winner)
from nobel
group by subject;
8、對每一個獎項(Subject),列出有多少年曾頒發過。
select subject,count(distinct yr)
from nobel
group by subject;
9、列出哪年曾同年有3個物理獎Physics得獎者。
select yr
from nobel
where subject='Physics'
group by yr
having count(winner)=3;
10、列出誰得獎多於一次。
select winner
from nobel
group by winner
having count(*)>1;
11、列出誰獲得多於一個獎項(Subject)
select winner
from nobel
group by winner
having count(distinct subject)>1;
12、哪年哪獎項，是同一獎項(subject)頒發給3個人。只列出2000年及之後的資料。
select yr,subject
from nobel
where yr>=2000
group by yr,subject
having count(winner)=3;
六、JOIN
1、修改此SQL以列出 賽事編號matchid 和球員名 player ,該球員代表德國隊Germany入球的。要找出德國隊球員，要檢查: teamid = 'GER'
select matchid,player
from goal
where teamid='GER';
2、只顯示賽事1012的 id, stadium, team1, team2
select id,stadium,team1,team2
from game
where id=1012;
3、修改它來顯示每一個德國入球的球員名，隊伍名，場館和日期。
select player,teamid,stadium,mdate
from game join goal on id=matchid
where teamid='GER';
4、列出球員名字叫Mario (player LIKE 'Mario%')有入球的 隊伍1 team1, 隊伍2 team2 和 球員名 player
select team1,team2,player
from game join goal on id=matchid
where player like 'Mario%';
5、列出每場球賽中首10分鐘gtime<=10有入球的球員 player, 隊伍teamid, 教練coach, 入球時間gtime
select player,teamid,coach,gtime
from goal join eteam on teamid=id
where gtime<=10;
6、列出'Fernando Santos'作為隊伍1 team1 的教練的賽事日期，和隊伍名。
select mdate,teamname
from game join eteam on team1=eteam.id
where coach='Fernando Santos';
7、列出場館 'National Stadium, Warsaw'的入球球員。
select player
from game join goal on id=matchid
where stadium= 'National Stadium, Warsaw';
8、只列出全部賽事，射入德國龍門的球員名字。
select distinct player
from game join goal on id=matchid
where teamid!='GER' and (team1='GER' or team2='GER');
注：1、选择有德国队参赛的比赛；2、排除掉德国队的进球
9、列出隊伍名稱 teamname 和該隊入球總數
select teamname,count(*)
from goal join eteam on teamid=id
group by teamname;
10、列出場館名和在該場館的入球數字。
select stadium,count(*)
from game join goal on id=matchid
group by stadium;
11、每一場波蘭'POL'有參與的賽事中，列出賽事編號 matchid, 日期date 和入球數字。
select matchid,mdate,count(*)
from game join goal on id=matchid
where team1='POL' or team2='POL'
group by matchid,mdate;
12、每一場德國'GER'有參與的賽事中，列出賽事編號 matchid, 日期date 和德國的入球數字。
select matchid,mdate,count(teamid)
from game join goal on id=matchid
where teamid='GER' and (team1='GER' or team2='GER')
group by matchid,mdate;
13、List every match with the goals scored by each team as shown. This will use "CASE WHEN" which has not been explained in any previous exercises.
select mdate,team1,sum(case when teamid=team1 then 1 else 0 end) score1,
team2,sum(case when teamid=team2 then 1 else 0 end) score2
from game left join goal on id=matchid
group by mdate,team1,team2;
注：因为比赛存在双方均未进球的可能，因此采用左连接
六、Old JOIN Tutorial
1、Show the athelete (who) and the country name for medal winners in 2000.
select who,name
from ttms join country on country=id
where games=2000;
2、Show the who and the color of the medal for the medal winners from 'Sweden'.
select who,color
from ttms join country on country=id
where name='Sweden';
3、Show the years in which 'China' won a 'gold' medal.
select games
from ttms join country on country=id
where name='China' and color='gold';
4、Show who won medals in the 'Barcelona' games.
select who
from ttws join games on games=yr
where city='Barcelona';
5、Show which city 'Jing Chen' won medals. Show the city and the medal color.
select city,color
from ttws join games on games=yr
where who='Jing Chen';
6、Show who won the gold medal and the city.
select who,city
from ttws join games on games=yr
where color='gold';
7、Show the games and color of the medal won by the team that includes 'Yan Sen'.
select games,color
from ttmd join team on team=id
where name='Yan Sen';
8、Show the 'gold' medal winners in 2004.
select name
from ttmd join team on team=id
where color='gold' and games=2004;
9、Show the name of each medal winner country 'FRA'.
select name
from ttmd join team on team=id
where country='FRA';
七、More JOIN operations
1、列出1962年首影的電影， [顯示 id, title]
select id,title
from movie
where yr=1962;
2、電影大國民 'Citizen Kane' 的首影年份。
select yr
from movie
where title='Citizen Kane' ;
3、列出全部Star Trek星空奇遇記系列的電影，包括id, title 和 yr(此系統電影都以Star Trek為電影名稱的開首)。按年份順序排列。
select id,title,yr
from movie
where title like 'Star Trek%';
4、id是 11768, 11955, 21191 的電影是什麼名稱?
select title
from movie
where id in (11768,11955,21191);
5、女演員'Glenn Close'的編號 id是什麼?
select id
from actor
where name='Glenn Close';
6、電影北非諜影'Casablanca' 的編號 id是什麼?
select id
from movie
where title='Casablanca' ;
7、列出電影北非諜影 'Casablanca'的演員名單。
select a.name
from actor a join casting c on a.id=c.actorid
join movie m on c.movieid=m.id
where m.title= 'Casablanca';
8、顯示電影異型'Alien' 的演員清單。
select a.name
from actor a join casting c on a.id=c.actorid
join movie m on c.movieid=m.id
where m.title='Alien';
9、列出演員夏里遜福 'Harrison Ford' 曾演出的電影。
select m.title
from movie m join casting c on m.id=c.movieid
join actor a on c.actorid=a.id
where a.name= 'Harrison Ford' ;
10、列出演員夏里遜福 'Harrison Ford' 曾演出的電影，但他不是第1主角。
select m.title
from movie m join casting c on m.id=c.movieid
join actor a on c.actorid=a.id
where a.name= 'Harrison Ford' and c.ord<>1;
11、列出1962年首影的電影及它的第1主角。
select m.title,a.name
from movie m join casting c on m.id=c.movieid
join actor a on c.actorid=a.id
where m.yr=1962 and c.ord=1;
12、尊·特拉華達'John Travolta'最忙是哪一年? 顯示年份和該年的電影數目。
select m.yr,count(*)
from movie m join casting c on m.id=c.movieid
join actor a on c.actorid=a.id
where a.name='John Travolta'
group by m.yr
order by count(*) desc
limit 0,1;
13、列出演員茱莉·安德絲'Julie Andrews'曾參與的電影名稱及其第1主角。
select m.title,a.name
from movie m join casting c on m.id=c.movieid
join actor a on c.actorid=a.id
where m.id in (select m.id
from movie m join casting c on m.id=c.movieid
join actor a on c.actorid=a.id
where a.name='Julie Andrews')
and c.ord=1;
14、列出按字母順序，列出哪一演員曾作30次第1主角。
select a.name
from actor a join casting c on a.id=c.actorid
where c.ord=1
group by a.name
having count(c.movieid)>=30
order by a.name asc;
15、列出1978年首影的電影名稱及角色數目，按此數目由多至少排列。
select m.title,count(c.actorid)
from movie m join casting c on m.id=c.movieid
where m.yr=1978
group by m.title
order by count(c.actorid) desc,m.title;
有时正确，有时错误的
16、列出曾與演員亞特·葛芬柯'Art Garfunkel'合作過的演員姓名。
select distinct a.name
from actor a join casting c on a.id=c.actorid
where c.movieid in(select c.movieid
from casting c join actor a on c.actorid=a.id
where a.name='Art Garfunkel')
and a.name<>'Art Garfunkel';
八、Using NULL
1、List the teachers who have NULL for their department.
select name
from teacher
where dept is null;
2、Note the INNER JOIN misses the teachers with no department and the departments with no teacher.
SELECT teacher.name, dept.name
FROM teacher INNER JOIN dept
ON (teacher.dept=dept.id);
3、Use a different JOIN so that all teachers are listed.
select t.name,d.name
from teacher t left join dept d
on t.dept=d.id;
4、Use a different JOIN so that all departments are listed.
select t.name,d.name
from teacher t right join dept d
on t.dept=d.id;
5、Use COALESCE to print the mobile number. Use the number '07986 444 2266' if there is no number given. Show teacher name and mobile number or '07986 444 2266'
select t.name,coalesce(t.mobile, '07986 444 2266')
from teacher t;
6、Use the COALESCE function and a LEFT JOIN to print the teacher name and department name. Use the string 'None' where there is no department.
select t.name,coalesce(d.name,'None')
from teacher t left join dept d
on t.dept=d.id;
7、Use COUNT to show the number of teachers and the number of mobile phones.
select count(id),count(mobile)
from teacher;
8、Use COUNT and GROUP BY dept.name to show each department and the number of staff. Use a RIGHT JOIN to ensure that the Engineering department is listed.
select d.name,count(t.id)
from teacher t right join dept d
on t.dept=d.id
group by d.name;
9、Use CASE to show the name of each teacher followed by 'Sci' if the teacher is in dept 1 or 2 and 'Art' otherwise.
select t.name,
case when d.id in (1,2) then 'Sci'
else 'Art' end
from teacher t left join dept d
on t.dept=d.id;
10、Use CASE to show the name of each teacher followed by 'Sci' if the teacher is in dept 1 or 2, show 'Art' if the teacher's dept is 3 and 'None' otherwise.
select t.name,
case when d.id in (1,2) then 'Sci'
when d.id=3 then 'Art'
else 'None' end
from teacher t left join dept d
on t.dept=d.id;
九、Self join
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 route left join stops
on stop=id
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 in (149,53)
group by company,num
having count(*)=2;
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=(select id from stops where name='Craiglockhart')
and b.stop=(select id from stops where name='London Road')
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,a.num
from route a join route b
on a.num=b.num and a.company=b.company
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,a.num
from route a join route b
on a.num=b.num and a.company=b.company
where a.stop=(select id from stops where name='Craiglockhart')
and b.stop=(select id from stops where 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.
from route r1 join stops s1
on r1.stop=s1.id
join route r2
on r1.num=r2.num and r1.company=r2.company
join stops s2
on r2.stop=s2.id
where s1.name= 'Craiglockhart' and r2.company='LRT';
10、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.
select First.num,First.company,s.name,Second.num,Second.company
from (select r1.num,r1.company,r2.stop
from route r1 join route r2
on r1.num=r2.num and r1.company=r2.company
where r1.stop=(select id from stops where name='Craiglockhart') )as First
join
(select r3.num,r3.company,r3.stop
from route r3 join route r4
on r3.num=r4.num and r3.company=r4.company
where r4.stop=(select id from stops where name='Lochend')) as Second
on First.stop=Second.stop
join stops s on Second.stop=s.id;
思路：1、确定从Craiglockhart出发的车以及途径站(select r1.num,r1.company,r2.stop from route r1 join route r2 on r1.num=r2.num and r1.company=r2.company where r1.stop=(select id from stops where name='Craiglockhart') )as First
2、确定到Lochend终点的车以及上车站 (select r3.num,r3.company,r3.stop from route r3 join route r4 on r3.num=r4.num and r3.company=r4.company where r4.stop=(select id from stops where name='Lochend')) as Second
3、First的下车站(中转站)与Second的上车站(中转站)一致First join Second on First.stop=Second.stop
4、将上表与stops连接，得到中转站名称。
扫一扫
2934
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
