The JOIN operation/zh
Language: | English • 日本語 • 中文 |
---|
id(編號) | mdate(日期) | stadium(場館) | team1(隊伍1) | team2(隊伍2) |
---|---|---|---|---|
1001 | 8 June 2012 | National Stadium, Warsaw | POL | GRE |
1002 | 8 June 2012 | Stadion Miejski (Wroclaw) | RUS | CZE |
1003 | 12 June 2012 | Stadion Miejski (Wroclaw) | GRE | CZE |
1004 | 12 June 2012 | National Stadium, Warsaw | POL | RUS |
... |
matchid(賽事編號) | teamid(隊伍編號) | player(入球球員) | gtime(入球時間) | |
---|---|---|---|---|
1001 | POL | Robert Lewandowski | 17 | |
1001 | GRE | Dimitris Salpingidis | 51 | |
1002 | RUS | Alan Dzagoev | 15 | |
1001 | RUS | Roman Pavlyuchenko | 82 | |
... |
id(編號) | teamname(隊名) | coach(教練) | ||
---|---|---|---|---|
POL | Poland | Franciszek Smuda | ||
RUS | Russia | Dick Advocaat | ||
CZE | Czech Republic | Michal Bilek | ||
GRE | Greece | Fernando Santos | ||
... |
合拼表格-- 歐洲國家盃 UEFA EURO 2012
此教程是介召 JOIN
的使用,讓你合拼2個或更多的表格。數據庫的表格貯存了在波蘭 Poland 和烏克欄 Ukraine的歐洲國家盃2012的賽事和入球資料。
第一個例子列出球員姓氏為'Bender'的入球數據。 *
表示列出表格的全部欄位,簡化了寫matchid, teamid, player, gtime
語句。
修改此SQL以列出 賽事編號matchid 和球員名 player ,該球員代表德國隊Germany入球的。要找出德國隊球員,要檢查:
teamid = 'GER'
SELECT * FROM goal
WHERE player LIKE '%Bender'
SELECT matchid, player
FROM goal
WHERE teamid LIKE 'GER'
由以上查詢,你可見Lars Bender's 於賽事 1012入球。.現在我們想知道此賽事的對賽隊伍是哪一隊。
留意在 goal
表格中的欄位 matchid
,是對應表格game
的欄位id
。我們可以在表格 game中找出賽事1012的資料。
只顯示賽事1012的 id, stadium, team1, team2
SELECT id,stadium,team1,team2
FROM game
SELECT id,stadium,team1,team2
FROM game
WHERE id=1012
我們可以利用JOIN
來同時進行以上兩個步驟。
SELECT * FROM game JOIN goal ON (id=matchid)
語句FROM 表示合拼兩個表格game 和 goal的數據。語句 ON 表示如何找出 game中每一列應該配對goal中的哪一列 -- goal的 id 必須配對game的 matchid 。 簡單來說,就是 ON (game.id=goal.matchid)
以下SQL列出每個入球的球員(來自goal表格)和場館名(來自game表格)
修改它來顯示每一個德國入球的球員名,隊伍名,場館和日期。
SELECT player,stadium
FROM game JOIN goal ON (id=matchid)
SELECT player,teamid,stadium,mdate
FROM game JOIN goal ON (id=matchid)
WHERE teamid='GER'
使用上題相同的 JOIN
語句,
列出球員名字叫Mario (player LIKE 'Mario%'
)有入球的 隊伍1 team1, 隊伍2 team2 和 球員名 player
SELECT team1, team2, player
FROM game JOIN goal ON (id=matchid)
WHERE player LIKE 'Mario%'
表格eteam
貯存了每一國家隊的資料,包括教練。你可以使用語句 goal JOIN eteam on teamid=id
來合拼 JOIN
表格goal
到 表格eteam
。
列出每場球賽中首10分鐘gtime<=10
有入球的球員 player
, 隊伍teamid
, 教練coach
, 入球時間gtime
SELECT player, teamid, gtime
FROM goal
WHERE gtime<=10
SELECT player, teamid, coach, gtime
FROM goal JOIN eteam ON (teamid=id)
WHERE gtime<=10
要合拼JOIN
表格game
和表格 eteam
,你可以使用
game JOIN eteam ON (team1=eteam.id)
或
game JOIN eteam ON (team2=eteam.id)
注意欄位id
同時是表格game
和表格 eteam
的欄位,你要清楚指出eteam.id
而不是只用id
列出'Fernando Santos'作為隊伍1 team1 的教練的賽事日期,和隊伍名。
SELECT mdate,teamname
FROM game JOIN eteam ON (team1=eteam.id)
WHERE coach='Fernando Santos'
列出場館 'National Stadium, Warsaw'的入球球員。
SELECT player
FROM goal JOIN game ON (id=matchid)
WHERE stadium = 'National Stadium, Warsaw'
更困難的題目
修改它,只列出全部賽事,射入德國龍門的球員名字。
找非德國球員的入球,德國可以在賽事中作team1 隊伍1(主)或team2隊伍2(客)。
你可以用teamid!='GER'
來防止列出德國球員。
你可以用DISTINCT
來防止球員出現兩次以上。
SELECT player, gtime
FROM game JOIN goal ON matchid = id
WHERE (team1='GER' AND team2='GRE')
SELECT DISTINCT player
FROM game JOIN goal ON matchid = id
WHERE (team1 = 'GER' OR team2 = 'GER')
AND teamid!='GER'
你應該在SELECT語句中使用COUNT(*)和使用GROUP BY teamname
SELECT teamname, player
FROM eteam JOIN goal ON id=teamid
ORDER BY teamname
SELECT teamname,COUNT(teamid)
FROM eteam JOIN goal ON id=teamid
GROUP BY teamname
SELECT stadium,COUNT(1)
FROM goal JOIN game ON id=matchid
GROUP BY stadium
SELECT matchid,mdate, team1, team2,teamid
FROM game JOIN goal ON matchid = id
WHERE (team1 = 'POL' OR team2 = 'POL')
SELECT matchid,mdate,COUNT(teamid)
FROM game JOIN goal ON matchid = id
WHERE (team1 = 'POL' OR team2 = 'POL')
GROUP BY matchid,mdate
SELECT matchid,mdate,COUNT(teamid)
FROM game JOIN goal ON matchid = id
WHERE (teamid='GER')
GROUP BY matchid,mdate
mdate | team1 | score1 | team2 | score2 |
---|---|---|---|---|
1 July 2012 | ESP | 4 | ITA | 0 |
10 June 2012 | ESP | 1 | ITA | 1 |
10 June 2012 | IRL | 1 | CRO | 3 |
... |
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,
CASE WHEN teamid=team1 THEN 1 ELSE 0 END score1
FROM game JOIN goal ON matchid = id
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
The next tutorial about the Movie database involves some slightly more complicated joins.