SQLZOO (7)

素颜马尾好姑娘i 2023-10-09 15:31 14阅读 0赞

## SQLZOO (7) ##

#### The JOIN operation ####

![在这里插入图片描述][611dbb017d0b4eaf836279ee2644d28b.png_pic_center]  
1.显示德国队打进的所有进球的比赛号和球员名称。

SELECT matchid,player FROM goal 
      WHERE teamid = 'GER'

![在这里插入图片描述][af2a1f110edc427cbdda104348bc2f8c.png_pic_center]  
2.仅查询id是1012 ，显示id，球场，球队1，球队2

SELECT id,stadium,team1,team2
      FROM game where id = 1012

![在这里插入图片描述][552e5a02c1044cdea16d8ab0cfe2d2dc.png_pic_center]  
3.显示球员，球队，球场和日期 为每一个德国队的进球。

SELECT player,teamid,stadium,mdate
      FROM game JOIN goal ON game.id=goal.matchid where teamid = 'GER'

![在这里插入图片描述][902a82d38bd44552a29baa6c47502543.png_pic_center]  
4.显示球队1，球队2和球员，每一个进球都是由一个叫马里奥的球员

select team1,team2,player 
         from game inner join goal 
          on game.id = goal.matchid 
          where player like 'Mario%'

![在这里插入图片描述][569b90c2fe3f49919cf8f2430f35c7f5.png_pic_center]

5.显示前10分钟内所有进球的球员、队员、教练和进球time

select player,teamid,coach,gtime 
       from eteam inner join goal 
        on goal.teamid= eteam.id 
        where gtime<=10

![在这里插入图片描述][8fc8d41f8f564359972e635038e2a1a8.png_pic_center]  
6.列出比赛日期和“费尔南多·桑托斯”担任球队1教练的球队名称。

select mdate,teamname 
        from eteam inner join game 
          on eteam.id = game.team1 
          where coach= 'Fernando Santos'

![在这里插入图片描述][c142676330284a6abad2bd74a827fd0b.png_pic_center]  
7.列出在体育场为“华沙国家体育场”的比赛中每进一球的球员

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

![在这里插入图片描述][6932203665e14dd4b8adc034925a7281.png_pic_center]  
8.显示所有对德国队进球的球员的名字。

select distinct(player) 
         from game inner join goal 
          on game.id = goal.matchid 
          where (team1='GER' or team2 = 'GER') and teamid != 'GER'

![在这里插入图片描述][b6fcbd8b45af43daa785adcf617e95f5.png_pic_center]  
9.显示球队名称和进球总数。

select teamname,count(*) 
          from eteam inner join goal 
          on eteam.id = goal.teamid 
           group by teamname

![在这里插入图片描述][cc335ef0c2ab47ebbb511a3c5fae42f0.png_pic_center]  
10.展示球场和每个球场的进球数。

select stadium,count(*) 
         from game inner join goal 
          on game.id = goal.matchid 
          group by stadium

![在这里插入图片描述][e0a9850316534847a243d97ac011e8f7.png_pic_center]  
11.对于每一场涉及“POL”的比赛，显示比赛号，日期和进球数。

select id,mdate,count(*) 
        from game inner join goal 
         on game.id = goal.matchid  
         where team1 = 'POL' or team2 = 'POL' 
         group by id,mdate

![在这里插入图片描述][3835f9d7e0e546f98b6e18a7cdd1454f.png_pic_center]  
12.对于“GER”进球的每一场比赛，显示比赛号，比赛日期和“GER”的进球数

select id,mdate,count(*) 
           from game inner join goal 
           on game.id = goal.matchid 
           where teamid = 'GER' and(team1 = 'GER' or team2 = 'GER')
            group by id,mdate

![在这里插入图片描述][4080d2bb5f7242099ec19644b2f3b6fa.png_pic_center]

13.![在这里插入图片描述][f0833ea52b6e4a8186f891c5c6e86d5f.png_pic_center]

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

![在这里插入图片描述][b217c934ef1b4dd8b7f4afa74d2a357d.png_pic_center]

[611dbb017d0b4eaf836279ee2644d28b.png_pic_center]: https://img-blog.csdnimg.cn/611dbb017d0b4eaf836279ee2644d28b.png#pic_center
[af2a1f110edc427cbdda104348bc2f8c.png_pic_center]: https://img-blog.csdnimg.cn/af2a1f110edc427cbdda104348bc2f8c.png#pic_center
[552e5a02c1044cdea16d8ab0cfe2d2dc.png_pic_center]: https://img-blog.csdnimg.cn/552e5a02c1044cdea16d8ab0cfe2d2dc.png#pic_center
[902a82d38bd44552a29baa6c47502543.png_pic_center]: https://img-blog.csdnimg.cn/902a82d38bd44552a29baa6c47502543.png#pic_center
[569b90c2fe3f49919cf8f2430f35c7f5.png_pic_center]: https://img-blog.csdnimg.cn/569b90c2fe3f49919cf8f2430f35c7f5.png#pic_center
[8fc8d41f8f564359972e635038e2a1a8.png_pic_center]: https://img-blog.csdnimg.cn/8fc8d41f8f564359972e635038e2a1a8.png#pic_center
[c142676330284a6abad2bd74a827fd0b.png_pic_center]: https://img-blog.csdnimg.cn/c142676330284a6abad2bd74a827fd0b.png#pic_center
[6932203665e14dd4b8adc034925a7281.png_pic_center]: https://img-blog.csdnimg.cn/6932203665e14dd4b8adc034925a7281.png#pic_center
[b6fcbd8b45af43daa785adcf617e95f5.png_pic_center]: https://img-blog.csdnimg.cn/b6fcbd8b45af43daa785adcf617e95f5.png#pic_center
[cc335ef0c2ab47ebbb511a3c5fae42f0.png_pic_center]: https://img-blog.csdnimg.cn/cc335ef0c2ab47ebbb511a3c5fae42f0.png#pic_center
[e0a9850316534847a243d97ac011e8f7.png_pic_center]: https://img-blog.csdnimg.cn/e0a9850316534847a243d97ac011e8f7.png#pic_center
[3835f9d7e0e546f98b6e18a7cdd1454f.png_pic_center]: https://img-blog.csdnimg.cn/3835f9d7e0e546f98b6e18a7cdd1454f.png#pic_center
[4080d2bb5f7242099ec19644b2f3b6fa.png_pic_center]: https://img-blog.csdnimg.cn/4080d2bb5f7242099ec19644b2f3b6fa.png#pic_center
[f0833ea52b6e4a8186f891c5c6e86d5f.png_pic_center]: https://img-blog.csdnimg.cn/f0833ea52b6e4a8186f891c5c6e86d5f.png#pic_center
[b217c934ef1b4dd8b7f4afa74d2a357d.png_pic_center]: https://img-blog.csdnimg.cn/b217c934ef1b4dd8b7f4afa74d2a357d.png#pic_center