http://sqlzoo.net/wiki/The_JOIN_operation/zh
Game (id , mdate ,stadium , team1 , team2)
Goal (matched ,teamid , player , gtime )
Eteam ( id ,teamname , coach )
1、第一個(gè)例子列出球員姓氏為'Bender'的入球數(shù)據(jù)。 * 表示列出表格的全部欄位，簡化了寫matchid, teamid, player,
gtime語句。修改此SQL以列出 賽事編號matchid 和球員名 player ,該球員代表德國隊(duì)Germany入球的。要找出德國隊(duì)球員，要檢查: teamid = 'GER'
Select matchid,player from goal
Where teamid = 'GER'
2.由以上查詢，你可見Lars Bender's 於賽事 1012入球。.現(xiàn)在我們想知道此賽事的對賽隊(duì)伍是哪一隊(duì)。留意在 goal 表格中的欄位 matchid ，是對應(yīng)表格game的欄位id。我們可以在表格game中找出賽事1012的資料。只顯示賽事1012的id, stadium, team1, team2
Select id,stadium,team1,team2 from game
Where id=1012
3.我們可以利用JOIN來同時(shí)進(jìn)行以上兩個(gè)步驟。
SELECT *
? FROM game JOINgoal ON (id=matchid)
語句FROM 表示合拼兩個(gè)表格game 和 goal的數(shù)據(jù)。語句 ON表示如何找出 game中每一列應(yīng)該配對goal中的哪一列 -- goal的 id 必須配對game的 matchid 。 簡單來說，就是ON (game.id=goal.matchid)。
以下SQL列出每個(gè)入球的球員(來自goal表格)和場館名(來自game表格)，修改它來顯示每一個(gè)德國入球的球員名，隊(duì)伍名，場館和日期。
解題：原題要求顯示出每一個(gè)德國入球的球員名、隊(duì)伍名、場館和日期。
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%')有入球的隊(duì)伍1 team1, 隊(duì)伍2 team2 和 球員名player
Select game.team1, game .team2,goal.player
From game joingoal
On goal.playerlike 'Mario%'AND game.id=goal.matchid
5.表格eteam 貯存了每一國家隊(duì)的資料，包括教練。你可以使用語句 goal JOIN eteam on teamid=id來合拼 JOIN 表格goal 到 表格eteam。列出每場球賽中首10分鐘gtime<=10有入球的球員 player, 隊(duì)伍teamid, 教練coach, 入球時(shí)間gtime。
Selectgoal.player,goal.teamid,eteam.coach,goal.gtime
From goal joineteam
Oneteam.id=goal.teamid and goal.gtime<=10
6.列出'Fernando Santos'作為隊(duì)伍1 team1 的教練的賽事日期，和隊(duì)伍名。
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的八強(qiáng)賽事的入球
修改它，只列出全部賽事，射入德國龍門的球員名字。
Select distinct(player),matchid
from goal joingame
On matchid =game.id and teamid!='GER'and (team1='GER' or team2='GER')
說明：求射入德國球門的球員名字，則參賽兩隊(duì)必然有一方為“德國”，其次射入德國球門的球員必然不是“德國”對的，應(yīng)該是“德國”隊(duì)的對手，所以入球球員的隊(duì)伍編號不能為“德國”“GER”，所以必須加上條件 teamid！=“GER”
9.列出隊(duì)伍名稱 teamname 和該隊(duì)入球總數(shù)
SELECTteamname,count(player)
From eteam leftjoin goal
On eteam.id=teamid
Group by teamname
10.列出場館名和在該場館的入球數(shù)字。
Selectstadium,count(player)
From game leftjoin goal
On game.id=matchid
Group by stadium
11.每一場波蘭'POL'有參與的賽事中，列出賽事編號 matchid, 日期date 和入球數(shù)字。
Selectmatchid,mdate,count(PLAYER)
From game joingoal
On matchid=game.idand (team1='pol'or team2='pol')
Group bymatchid,MDATE
說明：根據(jù)原題，但凡有波蘭參加的比賽，列出賽事編號、日期和入球次數(shù)，注意此處入球方可以不是波蘭隊(duì)自己。
12.每一場德國'GER'有參與的賽事中，列出賽事編號 matchid, 日期date 和德國的入球數(shù)字。
selectmatchid,mdate,count(teamid)
FROM game innerjoin goal
ongame.id=goal.matchid and(team1='ger'or team2='ger') and teamid='GER'
group bymatchid,mdate
說明：原題要求列出的是德國的入球次數(shù)，所以此處必須添加條件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.
