sqlzoo-day4&5

【目标：完成聚合函数，明天做NULL】发现聚合函数有点难，之前完全忘了join多个表怎么写，看之前笔记：有INNER(默认可不写)/OUTER JOIN；外联结LEFT (OUTER可省略) JOIN/RIGHT JOIN，USING 以及CROSS JOIN还有UNION，以上的知识点忘却了，进行练习并最好进行理解！子查询不熟练qwq

1. 足球赛数据库

T3：展示德国队入球的球员名、队伍名、场馆和日期。

如果是队伍名，还需要连接eteam表，但是答案显示只需要展示teamid即可。三个表的连接只需要再加一行JOIN 表 ON (条件)【注意联结的表越多容易消耗更多的资源】

SELECT goal.player,eteam.teamname, game.stadium, game.mdate
FROM game 
JOIN goal ON (game.id = goal.matchid)
JOIN eteam ON (goal.teamid = eteam.id)
WHERE goal.teamid = 'GER'

T8: 列出射入德国球门的队员name。

在game表中team1或者team2=GER，但是goal表中teamid!=GER，不加DISTINCT会有一个结果重复。

SELECT DISTINCT(goal.player)
FROM game JOIN goal ON goal.matchid = game.id 
WHERE (goal.teamid != 'GER') AND (game.team1='GER' OR game.team2='GER') 

T9: 列出队伍名称以及该队入球总数

JOIN 中使用GROUP BY进行汇总求和，ORDER BY是排序

SELECT eteam.teamname, COUNT(goal.player) AS num_goal
FROM eteam JOIN goal ON eteam.id = goal.teamid
GROUP BY teamname

T11: 列出波兰参与赛事中的赛事id，日期，和入球数字；

计算入球数字需要用上COUNT，对象是goal表，使用需要进行GROUP BY，因为比赛由matchid&mdate唯一确定，对matchid和mdate进行分组。

在GROUP BY之前可以将结果打印出来，再思考分组的规则。

--T11
SELECT goal.matchid,game.mdate, COUNT(goal.matchid) AS num
FROM game JOIN goal ON goal.matchid = game.id 
WHERE (team1 = 'POL' OR team2 = 'POL') 
GROUP BY goal.matchid, game.mdate

--T12
SELECT goal.matchid, game.mdate, COUNT(goal.teamid )
FROM game JOIN goal ON (game.id = goal.matchid)
WHERE (game.team1 = 'GER'OR game.team2 = 'GER') AND goal.teamid = 'GER'
GROUP BY goal.matchid, game.mdate, goal.teamid

T13: 列出每个队伍、以及他们的得分，还有比赛日期；

这里连接game与goal表，其中game表会包括两个队都未得分的情况，而默认的内连接JOIN只包含两张表都有条例，因此需要使用外联结game LEFT JOIN goal

SELECT mdate,
  team1,
  SUM(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) AS score1,
  team2,
  SUM(CASE WHEN teamid = team2 THEN 1 ELSE 0 END) AS score2
FROM game LEFT JOIN goal ON matchid = id
GROUP BY mdate, team1, team2
ORDER BY mdate, matchid, team1, team2

2. music数据库

T9: 找到大碟中每一首歌曲的价钱<0.5的，列出大碟名字、售卖和歌曲数量。

之前我只按title进行group，显示price不在分组里，在group还得加上price，因为having中要使用price所以在group中需要加入？【对group by的理解不够】

SELECT album.title, album.price, COUNT(track.song) FROM album
JOIN track ON (album.asin = track.album)
GROUP BY album.title, album.price
HAVING album.price/COUNT(track.song)<0.5

--T10:这题应该是还有一个排序，但是题目中没有说明,试了半天没出来，不管了
SELECT album.title,COUNT(album.song) FROM album
JOIN track ON (album.asin = track.album)
GROUP BY album.title
ORDER BY COUNT(album.song) DESC

3. movie数据库

T12：展示演员‘John Travolta’最忙的一年，以及该年的电影数目;

以为可以在GROUP BY的基础上将查询结果展示其中最大的，实际上这样无法出结果。需要调用上个查询的结果作为一张表再进行查询。答案中嵌套了两个SELECT语句，在HAVING语句中需要选出最大的，从group之后的表中选择最大，所以还要用一个SELECT。每个派生表必须有自己的别名。

SELECT movie.yr, COUNT(movie.title) AS num FROM movie
JOIN casting ON (movie.id = casting.movieid)
JOIN actor ON (casting.actorid = actor.id)
WHERE actor.name = 'John Travolta'
GROUP BY movie.yr
HAVING max(num)


-- answer
SELECT yr,COUNT(title) FROM movie 
JOIN casting ON movie.id=movieid
JOIN actor   ON actorid=actor.id
where name='John Travolta'
GROUP BY yr
HAVING 
    COUNT(title)=(SELECT MAX(c) FROM
                                (SELECT yr,COUNT(title) AS c FROM movie
                                 JOIN casting ON movie.id=movieid
                                 JOIN actor   ON actorid=actor.id
                                 where name='John Travolta'
                                 GROUP BY yr) AS t
                                )

T13: 很绕的题，列出演員茱莉·安德絲'Julie Andrews'曾參與的電影名稱及其第1主角。

逻辑：选出name='xx'的movieid，在movieid中选出ord=1的，返回actot的name。

又错了，不用再使用一次select选出ord，只需要在where语句加上ord=1的约束。

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


-- answer
select title,name
from (casting join actor on casting.actorid=actor.id) 
join movie on casting.movieid=movie.id
where  movieid in (select movieid
from casting join actor on casting.actorid=actor.id
where name='Julie Andrews') and ord=1

T14: 曾30次做第一主角的演员。

表示做第一主角≥30，之前想着再从最里面往外思考：首先选择ord=1的，group by 演员，然后作为子查询，选择里面大于等于30：WHERE （SELECT ...）>= 30被报错，还是正向思维，直接用HAVING即可筛选。被子查询伤到了，emmmm。

SELECT name FROM actor
JOIN casting ON (actor.id = actorid)
WHERE ord = 1
GROUP BY name
HAVING COUNT(*) >= 30

 T15: 选出与xx合作的演员。

首先需要选xx参演的电影，然后演员不是xx。

SELECT name FROM actor
JOIN casting ON (actor.id = actorid)
WHERE movieid IN (SELECT movieid FROM actor
JOIN casting ON (actor.id = actorid)
WHERE name = 'Art Garfunkel')
AND name != 'Art Garfunkel'

3. quiz2：

【在对数据库进行进行处理时，要看好哪些字段与什么相连。之前误以为还有第四个表director，结果它属于actor表的内容；而且有一些之前没看到过的用法↓】

T3：ORDER BY直接+数字n表示对SELECT的第n个字段进行排序。

T7：SELECT XX FROM table1， table2可以直接FROM多个表？

查了，from表源可以是一个表/多个表/视图/派生/联结表等

--T3: 
SELECT name, COUNT(movieid)
  FROM casting JOIN actor ON actorid=actor.id
 WHERE name LIKE 'John %'
 GROUP BY name ORDER BY 2 DESC
-- T7
SELECT title, yr 
   FROM movie, casting, actor 
  WHERE name='Robert De Niro' AND movieid=movie.id AND actorid=actor.id AND ord = 3