JOIN 表聯(lián)結(jié)
這次練習(xí)處理的是兩個以上表格數(shù)據(jù)蹂空，來自 2012 年波蘭和烏克蘭聯(lián)合舉辦的歐足聯(lián)男足錦標賽的所有比賽和進球的記錄。對應(yīng)數(shù)據(jù)的 mysql 版本傳送門： http://sqlzoo.net/euro2012.sql
知識點：SUM /COUNT/
Aggregates
1.統(tǒng)計世界總?cè)丝?/p>
例子：查詢——所有姓式是「Bender」的球員的所有進球記錄彼哼。
SELECT * FROM goal
WHERE player LIKE '%Bender'
* 指的是列出表格的所有列，更簡潔地語法代替——matchid, teamid, player, gtime
練習(xí)：查詢—— 德國隊的所有進球隊員及匹配的比賽 id
SELECT matchid, player FROM goal
WHERE teamid = 'GER'
根據(jù)之前的查詢，我們知道姜贡，「Lars Bender」在 id 為 1012 的比賽中進球撩穿。
查詢：這個比賽的對陣雙方是什么球隊磷支？
SELECT id,stadium,team1,team2
FROM game
WHERE id='1012'
3.JOIN
FROM：從 game 表合并，合并過來的表是 goal
ON：根據(jù)哪一行 game表是根據(jù) goal 的哪一行來合并
更具體的寫法是game.id=goal.matchid
查詢：德國每個進球所對應(yīng)的球員冗锁、球隊 id齐唆、體育場和比賽日期。
SELECT player, teamid, stadium, mdate
FROM game JOIN goal ON (id=matchid)
WHERE teamid='GER'
查詢：以「Mario 」為名字開頭的每個進球球員所對應(yīng)的球員&比賽隊伍
SELECT team1, team2, player
FROM game JOIN goal ON (id=matchid)
WHERE player LIKE 'Mario%'
SELECT player, teamid ,coach, gtime
FROM goal JOIN eteam on teamid=id
WHERE gtime<=10
SELECT mdate, teamname
FROM game JOIN eteam ON (team1=eteam.id)
WHERE coach='Fernando Santos'
SELECT player
FROM game JOIN goal ON(id=matchid)
WHERE stadium='National Stadium, Warsaw'
以下是更難的問題:
SELECT DISTINCT(player)
FROM game JOIN goal ON (matchid = id)
WHERE (team1='GER' OR team2='GER')
AND teamid != 'GER'
SELECT teamname, COUNT(player)
FROM eteam JOIN goal ON (id=teamid)
GROUP BY teamname
SELECT stadium, COUNT(player)
FROM game JOIN goal ON (id=matchid)
GROUP BY stadium
SELECT stadium, COUNT(player)
FROM game JOIN goal ON (id=matchid)
GROUP BY stadium
SELECT matchid,mdate,COUNT(teamid)
FROM game JOIN goal ON (matchid = id)
WHERE (teamid='GER')
GROUP BY matchid,mdate
知識點：CASE
CASE語法能夠讓你根據(jù)不同的條件冻河，返回不同的值箍邮。
如果沒有匹配的條件，也沒有ELSE,將會返回RETURN
CASE WHEN condition1 THEN value1
WHEN condition2 THEN value2
ELSE def_value
END
SELECT matchid,mdate,COUNT(teamid)
FROM game JOIN goal ON (matchid = id)
WHERE (teamid='GER')
GROUP BY matchid,mdate
SELECT name, population
,CASE WHEN population<1000000
THEN 'small'
WHEN population<10000000
THEN 'medium'
ELSE 'large'
END
FROM bbc
SELECT mdate,
team1,
CASE WHEN teamid=team1 THEN 1 ELSE 0 END score1
FROM game JOIN goal ON matchid = id
SELECT stadium, COUNT(player)
FROM game JOIN goal ON (id=matchid)
GROUP BY stadium
