The JOIN operation/zh

http://sqlzoo.net/wiki/The_JOIN_operation/zh


Game (id , mdate ,stadium , team1 , team2)

Goal (matched ,teamid , player , gtime )

Eteam ( id ,teamname , coach )



1、第一個例子列出球員姓氏為'Bender'的入球數據。 * 表示列出表格的全部欄位,簡化了寫matchid, teamid, player,

gtime語句。修改此SQL以列出 賽事編號matchid 和球員名 player ,該球員代表德國隊Germany入球的。要找出德國隊球員,要檢查: teamid = 'GER'


Select matchid,player from goal

Where teamid = 'GER'


2.由以上查詢,你可見Lars Bender's 於賽事 1012入球。.現在我們想知道此賽事的對賽隊伍是哪一隊。留意在 goal 表格中的欄位 matchid ,是對應表格game的欄位id。我們可以在表格game中找出賽事1012的資料。只顯示賽事1012的id, stadium, team1, team2


Select id,stadium,team1,team2 from game

Where id=1012


3.我們可以利用JOIN來同時進行以上兩個步驟。

SELECT *

  FROM game JOINgoal ON (id=matchid)

語句FROM 表示合拼兩個表格game 和 goal的數據。語句 ON表示如何找出 game中每一列應該配對goal中的哪一列 -- goal的 id 必須配對game的 matchid 。 簡單來說,就是ON (game.id=goal.matchid)。


以下SQL列出每個入球的球員(來自goal表格)和場館名(來自game表格),修改它來顯示每一個德國入球的球員名,隊伍名,場館和日期。


解题:原题要求显示出每一个德国入球的球员名、队伍名、场馆和日期。


Select goal.player,goal.teamid,game.stadium,game.mdate

From game joingoal

On goal.matchid =game.id  and goal.teamid='GER'

或者

SELECTplayer,goal.teamid,stadium,mdate 

  FROM game JOIN goal

ONgoal.matchid=game.id and goal.teamid = 'GER'



4.使用上題相同的 JOIN語句,列出球員名字叫Mario (player LIKE 'Mario%')有入球的隊伍1 team1, 隊伍2 team2 和 球員名player


Select game.team1, game .team2,goal.player

From game joingoal

On goal.playerlike 'Mario%'AND game.id=goal.matchid



5.表格eteam 貯存了每一國家隊的資料,包括教練。你可以使用語句 goal JOIN eteam on teamid=id來合拼 JOIN 表格goal 到 表格eteam。列出每場球賽中首10分鐘gtime<=10有入球的球員 player, 隊伍teamid, 教練coach, 入球時間gtime。


Selectgoal.player,goal.teamid,eteam.coach,goal.gtime

From goal joineteam

Oneteam.id=goal.teamid and goal.gtime<=10


6.列出'Fernando Santos'作為隊伍1 team1 的教練的賽事日期,和隊伍名。


Select game.mdate,eteam.teamname

From game joineteam

On coach='Fernando

Santos'and eteam.id=team1



7、列出場館 'National Stadium, Warsaw'的入球球員。

Select player

From game join goal

On stadium='National Stadium, Warsaw' and game.id=matched



8.以下例子找出德國-希臘Germany-Greece的八強賽事的入球

修改它,只列出全部賽事,射入德國龍門的球員名字。


Select distinct(player),matchid

from goal joingame

On matchid =game.id and teamid!='GER'and (team1='GER' or team2='GER')


说明:求射入德国球门的球员名字,则参赛两队必然有一方为“德国”,其次射入德国球门的球员必然不是“德国”对的,应该是“德国”队的对手,所以入球球员的队伍编号不能为“德国”“GER”,所以必须加上条件 teamid!=“GER”


9.列出隊伍名稱 teamname 和該隊入球總數


SELECTteamname,count(player)

From eteam leftjoin goal

On eteam.id=teamid

Group by teamname


10.列出場館名和在該場館的入球數字。


Selectstadium,count(player)

From game leftjoin goal

On game.id=matchid

Group by stadium



11.每一場波蘭'POL'有參與的賽事中,列出賽事編號 matchid, 日期date 和入球數字。

Selectmatchid,mdate,count(PLAYER)

From game joingoal

On matchid=game.idand (team1='pol'or team2='pol')

Group bymatchid,MDATE


说明:根据原题,但凡有波兰参加的比赛,列出赛事编号、日期和入球次数,注意此处入球方可以不是波兰队自己。



12.每一場德國'GER'有參與的賽事中,列出賽事編號 matchid, 日期date 和德國的入球數字。

selectmatchid,mdate,count(teamid)

FROM game innerjoin goal

ongame.id=goal.matchid and(team1='ger'or team2='ger') and teamid='GER'

group bymatchid,mdate

说明:原题要求列出的是德国的入球次数,所以此处必须添加条件teamid=’ger’

13.疑问待解

Listevery 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 July2012  ESP 4     ITA  0

10 June2012      ESP 1     ITA  1

10 June2012      IRL  1     CRO      3

...

Noticein the query given every goal is listed. If it was a team1 goal then a 1appears in score1, otherwise there is a 0. You could SUM this column to get acount of the goals scored by team1. Sort your result by mdate, matchid, team1and team2.


推荐阅读更多精彩内容

  • The Inner Game of Tennis W Timothy Gallwey Jonathan Cape ...
    网事_79a3阅读 5,451评论 2 10
  • 自己最普通,但自己可以不同,这个社会需要的是什么,我还不知道,但学习的能力你必须有,这是你获得技能的关键所在,态度...
    库昊已用阅读 29评论 0 0
  • 方向很重要,认准了就努力前行吧,每一天都是生命中最年轻的时刻,坚持走就一定会到达远方。
    花汀阅读 29评论 0 0
  • 夏目漱石的猫 蜷缩在我的脚下 听他讲着故事 我开始有些奇怪 我怎么突然间能听懂猫的语言 当我听到精彩的地方 我情不...
    利君理疗家政阅读 45评论 0 0
  • 仓央嘉措问佛“世间为什么有那么多遗憾?” 佛曰:婆娑即遗憾。 日子总过的那么快,阴雨的冬季也是快速的掠了一下大地就...
    上北下喃阅读 793评论 0 1