casewhen多条件查询_【三】Sqlzoo 表关联与case when

SQL多表查询学习主要包括下列知识点：

• 表的加法
• 表的联结（包括：交叉联结、内联结、左联结、右联结、全联结）
• case when的表达式

• 内联结、左联结、右联结的示意文氏图：

1.show the name of all players who scored a goal against Germany

SELECT 

第一个条件：为了找出对手是德国的比赛的id，

第二个条件：为了找出在对手是德国时候进球的队员

链接表时候条件where是为了筛选出结果进行匹配

2.For every match involving 'POL', show the matchid, date and the number of goals scored.

SELECT matchid,mdate, COUNT(teamid)
FROM game JOIN goal ON matchid = id 
WHERE (team1 = 'POL' OR team2 = 'POL')  
GROUP BY matchid

这里错误地加上了matchid='POL' 导致结果少了 ,这里记分不管是哪个球队得分，都计入，如果多了matchid='POL'，就只计POL得分。

3.

有两列数据要分组汇总，而不是一列汇总

在sum函数里面用case when 表里列名=组1 THEN result1 ELSE result2 END

4.Obtain the cast list for the film 'Alien'

SELECT actor.name 
FROM actor 
INNER JOIN casting 
ON casting.actorid=actor.id 
INNER JOIN movie 
ON movie.id=casting.movieid -----这里直接联结上已经联好的表
WHERE title='Alien'----最后筛选

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

SELECT title, name FROM movie
JOIN casting ON movie.id=casting.movieid
JOIN actor on actor.id=casting.actorid
WHERE casting.movieid IN (                    ------（）里为julie参演的电影
SELECT movie.id from movie
JOIN casting ON movie.id=casting.movieid
JOIN actor ON actor.id=casting.actorid
WHERE actor.name='Julie Andrews') AND ord=1

6.Obtain a list, in alphabetical order, of actors who've had at least 15 starring roles.

SELECT name
FROM actor
WHERE id IN  (SELECT actorid
               FROM casting
               WHERE ord=1
               GROUP BY actorid
               HAVING COUNT(actorid)>=15)
ORDER BY name

HAVING +count 的用法

HAVING +聚合函数 count avg sum