sqlzoo答案--join
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 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,teamid,stadium,mdate
FROM game JOIN goal ON (id=matchid)
where teamid = 'GER'
4.
使用上題相同的 JOIN語句，
列出球員名字叫Mario (player LIKE 'Mario%')有入球的 隊伍1 team1, 隊伍2 team2 和 球員名 player
select team1,team2,player
from goal join game on (id=matchid)
where player LIKE 'Mario%'
5.
表格eteam 貯存了每一國家隊的資料，包括教練。你可以使用語句 goal JOIN eteam on teamid=id來合拼 JOIN 表格goal 到 表格eteam。
列出每場球賽中首10分鐘gtime<=10有入球的球員 player, 隊伍teamid, 教練coach, 入球時間gtime
SELECT player, teamid,coach, gtime
FROM goal join eteam on (teamid=id)
WHERE gtime<=10
6.
要合拼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'
7.
列出場館 'National Stadium, Warsaw'的入球球員。
select player
from goal join game on (matchid=id)
where stadium in ('National Stadium, Warsaw')
8.
修改它，只列出全部賽事，射入德國龍門的球員名字。
select distinct player
from goal join(select id from game
where team1='GER' or team2='GER') t on (matchid=id)
where teamid !='GER'
9.
select teamname,count(matchid)
from(SELECT teamname, matchid
FROM eteam JOIN goal ON (id=teamid)) t
group BY teamname
10.
select stadium,count(player)
from (select stadium,player
from game join goal on (id=matchid)) t
group by stadium
11.
select matchid,mdate,count(teamid)
from(SELECT matchid,mdate, team1, team2,teamid
FROM game JOIN goal ON matchid = id
WHERE (team1 = 'POL' OR team2 = 'POL')) t
group by matchid,mdate
12.
select matchid,mdate,count(teamid)
from(SELECT matchid,mdate, team1, team2,teamid
FROM game JOIN goal ON matchid = id
WHERE (team1 = 'GER' OR team2 = 'GER')) t
where teamid='GER'
group by matchid,mdate
13.
| 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,
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 JOIN goal ON (matchid = id)
group by mdate,matchid,team1,team2
/*sum可以直接括 case when ，group by可以有select中没有查询的字段*/
Old JOIN Tutorial
1.
Show the athelete (who) and the country name for medal winners in 2000.
SELECT who, country.name
FROM ttms JOIN country
ON (ttms.country=country.id)
WHERE games = 2000
2.
Show the who and the color of the medal for the medal winners from 'Sweden'.
ttms(games,color,who,country) country(id,name)
select who,color
from ttms join country
on (ttms.country=country.id)
where name='Sweden'
3.
Show the years in which 'China' won a 'gold' medal.
ttms(games,color,who,country) country(id,name)
select games
from ttms join country
on (ttms.country=country.id)
where color='gold' and name='China'
4.
Show who won medals in the 'Barcelona' games.
ttws(games,color,who,country) games(yr,city,country)
SELECT who
FROM ttws JOIN games
ON (ttws.games=games.yr)
WHERE city = 'Barcelona'
5.
Show which city 'Jing Chen' won medals. Show the city and the medal color.
ttws(games,color,who,country) games(yr,city,country)
select city,color
FROM ttws JOIN games
ON (ttws.games=games.yr)
where who='Jing Chen'
6.
Show who won the gold medal and the city.
ttws(games,color,who,country) games(yr,city,country)
select who,city
FROM ttws JOIN games
ON (ttws.games=games.yr)
where color='gold'
7.
Show the games and color of the medal won by the team that includes 'Yan Sen'.
ttmd(games,color,team,country) team(id,,name)
select games ,color
from ttmd join team
on (team=id)
where name like '%Yan Sen%'
8.
Show the 'gold' medal winners in 2004.
ttmd(games,color,team,country) team(id,,name)
select name
from ttmd join team
on (team=id)
where games='2004'and color='gold'
9.
Show the name of each medal winner country 'FRA'.
ttmd(games,color,team,country) team(id,,name)
select name
from ttmd join team
on (team=id)
where country='FRA'
