SQL的一系列问题_在每一個州中找出最大面積的國家,列出洲份 continent, 國家名字 name 及面積 area-CSDN博客

本文链接：https://blog.csdn.net/qq_33816775/article/details/87919197

最近在做SQLZOO熟悉SQL的语言规则与语法，里面有很多特别好用的技巧

1. 子查询参考外部查询的数值，并使用之

在每一個州中找出最大面積的國家，列出洲份 continent, 國家名字 name 及面積 area。 (有些國家的記錄中，AREA是NULL，沒有填入資料的。)

SELECT continent, name, area FROM world x
WHERE area >= ALL
(SELECT area FROM world y
WHERE y.continent = x.continent
AND area>0)

这种类型的查询可以理解为一般编程语言里面的循环嵌套，即通过y.continent = x.continent 保证了比较两部分比较的一致，从而使达到在某一项的内部进行比较。相同的还有一个题

有些國家的人口是同洲份的所有其他國的3倍或以上。列出國家名字name 和洲份 continent。

SELECT name ,continent FROM world x
WHERE population/3 >= ALL (SELECT population FROM world y
WHERE y.continent = x.continent
AND population >0
AND y.population <> x.population )

2. 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.

mdate	team1	score1	team2	score2
1 July 2012	ESP	4	ITA	0
10 June 2012	ESP	1	ITA	1
10 June 2012	IRL	1	CRO	3
...

Notice in the query given every goal is listed. If it was a team1 goal then a 1 appears in score1, otherwise there is a 0. You could SUM this column to get a count of the goals scored by team1. Sort your result by mdate, matchid, team1 and team2.

这里用到了LEFT JOIN

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 matchid = id
GROUP BY mdate,team1,team2

内连接INNER JOIN 返回的是两者的交集，LEFT JOIN返回的是左端的部分但仍然与右端连接，RIGHT JOIN反之，FULL JOIN返回的是两者的全部。这个例子中，如果用默认的JOIN无法得到比分为0:0 的情况，但0比分存在在game表中，不存在在goal表中，因此使用的是LEFT JOIN

3. 使用多层选择嵌套来获取目标信息

List the film title and the leading actor for all of the films 'Julie Andrews' played in.

Did you get "Little Miss Marker twice"?

Julie Andrews starred in the 1980 remake of Little Miss Marker and not the original(1934).

Title is not a unique field, create a table of IDs in your subquery

我的答案

SELECT title,name
FROM movie JOIN casting ON movie.id = movieid
JOIN actor ON actor.id = actorid
WHERE movieid IN(
SELECT movieid
FROM movie JOIN casting ON movie.id = movieid
JOIN actor ON actor.id = actorid
WHERE actorid IN (
SELECT id FROM actor
WHERE name='Julie Andrews'))
AND ord = 1

内层找到所有有关'Julie Andrews'参演的电影，外层找到相应的leading star，因为名字不是唯一的索引信息，因此使用ID

3. 比较难的一个题

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.

Hint

Self-join twice to find buses that visit Craiglockhart and Lochend, then join those on matching stops.

题目的问题是相当于使用SQL语言来编写一个公交中转路线的问题，但是复杂度很高。思路是通过SELF-JOIN两次来获取可用的中间站，然后选出目的地与出发地中相同的中间站，然后在通过JOIN把全部显示出来。但是比较麻烦的是如何把最后的结果呈现出来。我做的比较麻烦了，如下：