SQL学习(三)——JOIN SELECT WITHIN SELECT

JOIN

2020/01/31
https://sqlzoo.net/wiki/The_JOIN_operation

3.Modify it to show the player, teamid, stadium and mdate for every German goal.

SELECT goal.player,goal.teamid,game.stadium,game.mdate 
  FROM goal join game on game.id = goal.matchid
    where teamid = 'GER';

注：where在join后

7.List the player for every goal scored in a game where the stadium was 'National Stadium, Warsaw’

select player
 from goal JOIN game on matchid = id
 where stadium = 'National Stadium, Warsaw'

8.The example query shows all goals scored in the Germany-Greece quarterfinal.
Instead show the name of all players who scored a goal against Germany.

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

注：题目要求找出对阵德国队得分的球员：
1.需要球员表和比赛表
2.对阵德国队 说明有德国队(team1=‘GER’ OR team2=‘GER’)，但是是德国队对手teamid != ‘GER’

9.Show teamname and the total number of goals scored

SELECT teamname,count(player) 
  from goal join eteam on eteam.id = goal.teamid
  group by teamname

注：1.count() 括号内写啥都没变化，除非加distinct 2.在这里from goal jpin eteam 前后没有顺序

11.For every match involving ‘POL’, show the matchid, date and the number of goals scored.

SELECT matchid,mdate, count(gtime)
  FROM game JOIN goal ON matchid = id 
 WHERE (team1 = 'POL' OR team2 = 'POL')
 group by matchid,mdate

注：group by matchid 不可
解决方案：group by 后面是要跟着的 select 中所有不是聚合函数的字段，即除了count的字段

12.For every match where ‘GER’ scored, show matchid, match date and the number of goals scored by 'GER’

select matchid,mdate,count(*)
 from game join goal on matchid = id
 where((team1 = 'GER' OR team2 = 'GER') and (goal.teamid = '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.

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.

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, matchid,team1,team2

res:
注：1.要用sum，不要用count,因为sum()中的case when表示当goal表中的teamid是teamid，也就代表进一球，+1，但是else +0，如果是count，else也会算一条；

2. group by后面不加matchid没影响

MORE JOIN

2020/01/31
https://sqlzoo.net/wiki/More_JOIN_operations

6.Obtain the cast list for ‘Casablanca’.
what is a cast list?
The cast list is the names of the actors who were in the movie.
Use movieid=11768, (or whatever value you got from the previous question)

select name from actor
 where id in(select actorid from casting 
   where movieid = (select id from movie 
     where title = 'Casablanca'))

9.List the films where ‘Harrison Ford’ has appeared - but not in the starring role. [Note: the ord field of casting gives the position of the actor. If ord=1 then this actor is in the starring role]

select title from movie 
 where id in (select movieid from casting 
  where actorid in(select id from actor
    where name like '%Harrison Ford%') and ord!='1')

注:三表join,效果一样

SELECT title
  FROM movie JOIN casting 
    ON movie.id=movieid
        JOIN actor   
          ON actorid=actor.id
WHERE name like '%Harrison Ford%' and ord!=1

10.List the films together with the leading star for all 1962 films.

SELECT title,name
  FROM movie JOIN casting 
    ON movie.id=movieid
        JOIN actor   
          ON actorid=actor.id
WHERE yr=1962 AND ord=1

注:三表join

11.Which were the busiest years for ‘Rock Hudson’, show the year and the number of movies he made each year for any year in which he made more than 2 movies.

SELECT yr,COUNT(title) FROM
  movie JOIN casting ON movie.id=movieid
        JOIN actor   ON actorid=actor.id
WHERE actor.name='Rock Hudson'
GROUP BY yr
HAVING COUNT(title) > 2

注:三表join+group by

12.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=movieid
        JOIN actor   
          ON actorid=actor.id
WHERE movie.id IN (SELECT movieid
                     FROM casting
                    WHERE actorid IN (SELECT actor.id
                                        FROM actor
                                       WHERE name='Julie Andrews'))
      AND ord=1

注:三表join+SELECT WITHIN SELECT
通过select within select 确定where 的范围；再到三联表中拿出字段

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

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

注: select where ord=1确定范围 ；通过group by 分组，having 可以判断聚合函数进行筛选；

14.List the films released in the year 1978 ordered by the number of actors in the cast, then by title.

select title,count(actorid) from movie join casting 
  on movie.id = casting.movieid 
     join actor 
        on actor.id = casting.actorid 
 where yr = '1978'
 group by title
 order by  count(actorid)desc,title

注: 连表 where ord=1确定范围 ；由于要每部电影的演员数量，所以按照title 分组， 注意order by

15.List all the people who have worked with ‘Art Garfunkel’.

select distinct name from actor join casting on actorid = id
 where casting.movieid in(select movieid from casting join actor on actorid = id
 where actor.name =  'Art Garfunkel') and name != 'Art Garfunkel'
order by name

注:先找出Art参演的movie，再根据这个去找对应的合作演员
也可：

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

仔细体会 join和 select within select

left join inner join(join)

https://www.cnblogs.com/pcjim/articles/799302.html