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

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

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

1. SELECT continent, name, area FROM world x
2.   WHERE area >= ALL
3.     (SELECT area FROM world y
4.         WHERE y.continent = x.continent
5.           AND area>0)

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

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

1. SELECT name ,continent FROM world x
2. WHERE population/3 >= ALL (SELECT population FROM world y 
3.                            WHERE y.continent = x.continent 
4.                            AND population >0 
5.                            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

1. SELECT mdate,
2.   team1,
3.   SUM(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) score1,
4.   team2,
5.   SUM(CASE WHEN teamid=team2 THEN 1 ELSE 0 END) score2
6.   FROM game left JOIN goal ON matchid = id
7.   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

我的答案

1. SELECT title,name 
2. FROM movie JOIN casting ON movie.id = movieid
3.            JOIN actor ON actor.id = actorid
4. WHERE movieid IN(
5. SELECT movieid
6. FROM movie JOIN casting ON movie.id = movieid
7.            JOIN actor ON actor.id = actorid
8. WHERE actorid IN (
9.   SELECT id FROM actor
10.   WHERE name='Julie Andrews'))
11. 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把全部显示出来。但是比较麻烦的是如何把最后的结果呈现出来。我做的比较麻烦了，如下：

1. SELECT bus1.num, bus1.company,bus1.stop,bus2.num, bus2.company
2. FROM 
3. (SELECT a.num AS num, a.company AS company,stopb.name AS stop
4. FROM route a JOIN route b ON (a.company = b.company AND a.num = b.num)
5.              JOIN stops stopa ON (a.stop = stopa.id)
6.              JOIN stops stopb ON (b.stop = stopb.id)
7. WHERE stopa.name = 'Craiglockhart' AND stopb.name IN(
8. SELECT stopd.name
9. FROM route c JOIN route d ON (c.company = d.company AND c.num = d.num)
10.              JOIN stops stopc ON (c.stop = stopc.id)
11.              JOIN stops stopd ON (d.stop = stopd.id)
12. WHERE stopc.name = 'Lochend')) AS bus1
13. JOIN 
14. (SELECT a.num AS num, a.company AS company,stopb.name AS stop
15. FROM route a JOIN route b ON (a.company = b.company AND a.num = b.num)
16.              JOIN stops stopa ON (a.stop = stopa.id)
17.              JOIN stops stopb ON (b.stop = stopb.id)
18. WHERE stopa.name = 'Lochend' AND stopb.name IN(
19. SELECT stopd.name
20. FROM route c JOIN route d ON (c.company = d.company AND c.num = d.num)
21.              JOIN stops stopc ON (c.stop = stopc.id)
22.              JOIN stops stopd ON (d.stop = stopd.id)
23. WHERE stopc.name = 'Craiglockhart')) AS bus2
24. ON bus1.stop = bus2.stop 

网上的参考资料的答案看出来我确实写了很多冗余的部分（捂脸

修正以后是

1. SELECT bus1.num, bus1.company,bus1.stop,bus2.num, bus2.company
2. FROM 
3. (SELECT a.num AS num, a.company AS company,stopb.name AS stop
4. FROM route a JOIN route b ON (a.company = b.company AND a.num = b.num)
5.              JOIN stops stopa ON (a.stop = stopa.id)
6.              JOIN stops stopb ON (b.stop = stopb.id)
7. WHERE stopa.name = 'Craiglockhart') AS bus1
8. JOIN 
9. (SELECT a.num AS num, a.company AS company,stopb.name AS stop
10. FROM route a JOIN route b ON (a.company = b.company AND a.num = b.num)
11.              JOIN stops stopa ON (a.stop = stopa.id)
12.              JOIN stops stopb ON (b.stop = stopb.id)
13. WHERE stopa.name = 'Lochend' ) AS bus2
14. ON bus1.stop = bus2.stop 

这道题表明了SQL跟普通思路是不一样的（再次捂脸