The JOIN WC2026/ru

Language:Project:Language policy	English  • русский

game

id	mdate	stadium	team1	team2
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
...

goal

matchid	teamid	player	gtime
1001	POL	Robert Lewandowski	17
1001	GRE	Dimitris Salpingidis	51
1002	RUS	Alan Dzagoev	15
1002	RUS	Roman Pavlyuchenko	82
...

eteam

id	teamname	coach
POL	Poland	Franciszek Smuda
RUS	Russia	Dick Advocaat
CZE	Czech Republic	Michal Bilek
GRE	Greece	Fernando Santos
...

В этом руководстве рассматривается оператор JOIN, который позволяет использовать данные из двух или нескольких таблиц. Таблицы содержат сведения обо всех матчах и голах чемпионата Европы по футболу УЕФА 2012, проходившего в Польше и Украине.

Данные доступны в формате MySQL по адресу http://sqlzoo.net/euro2012.sql

Summary

Голы Бендера

В первом примере показан гол, забитый игроком по фамилии Бендер (Bender). Символ * означает, что нужно вывести все столбцы таблицы — это сокращённая запись для matchid, teamid, player, gtime

Измените запрос так, чтобы он выводил matchid и имя player для всех голов, забитых сборной Германии (Germany). Чтобы выбрать игроков сборной Германии (Germany), используйте условие: teamid = 'GER'

SELECT * FROM goal 
  WHERE player LIKE '%Bender'

SELECT matchid, player 
 FROM goal
   WHERE teamid LIKE 'GER'

Названия команд

Из предыдущего запроса видно, что Ларс Бендер (Lars Bender) забил гол в матче 1012. Теперь нужно узнать, какие команды играли в этом матче.

Обратите внимание, что столбец matchid в таблице goal соответствует столбцу id в таблице game. Информацию о матче 1012 можно получить, найдя соответствующую строку в таблице game.

Выведите id, stadium, team1 и team2 только для матча 1012

SELECT id,stadium,team1,team2
  FROM game

SELECT id,stadium,team1,team2
 FROM game
WHERE id=1012

JOIN

Эти два шага можно объединить в один запрос с помощью JOIN.

SELECT *
  FROM game JOIN goal ON (id=matchid)

Предложение FROM указывает, что данные из таблицы goal нужно объединить с данными из таблицы game. Условие ON определяет, какие строки таблицы game соответствуют строкам таблицы goal: значение matchid из таблицы goal должно совпадать со значением id из таблицы game. (Для большей ясности можно было бы записать условие так:
ON (game.id=goal.matchid)

Приведённый ниже код выводит имя игрока из таблицы goal и название стадиона из таблицы game для каждого забитого гола.

Измените запрос так, чтобы он выводил player, teamid, stadium и mdate для каждого гола сборной Германии (Germany).

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, что и в предыдущем задании.

Выведите team1, team2 и player для каждого гола, забитого игроком по имени Марио (Mario) player LIKE 'Mario%'

SELECT team1, team2, player
  FROM game JOIN goal ON (id=matchid)
WHERE player LIKE 'Mario%'

Ранние голы

Таблица eteam содержит сведения о каждой национальной сборной, включая тренера. Таблицы goal и eteam можно объединить с помощью выражения goal JOIN eteam on teamid=id

Выведите player, teamid, coach и gtime для всех голов, забитых в первые 10 минут gtime<=10

SELECT player, teamid, gtime
  FROM goal 
 WHERE gtime<=10

SELECT player, teamid, coach, gtime
  FROM goal JOIN eteam ON (teamid=id)
 WHERE gtime<=10

Фернанду Сантуш

Чтобы объединить таблицы game и eteam с помощью JOIN, можно использовать один из двух вариантов:
game JOIN eteam ON (team1=eteam.id) или game JOIN eteam ON (team2=eteam.id)

Обратите внимание: поскольку столбец id есть и в таблице game, и в таблице eteam, необходимо указывать eteam.id, а не просто id

Выведите даты матчей и название команды, в которой Фернанду Сантуш (Fernando Santos) был тренером team1.

SELECT mdate,teamname
  FROM game JOIN eteam ON (team1=eteam.id)
WHERE coach='Fernando Santos'

Матчи в Варшаве

Выведите player для каждого гола, забитого в матче на стадионе «Национальный стадион в Варшаве» (National Stadium, Warsaw)

SELECT player
  FROM goal JOIN game ON (id=matchid)
 WHERE stadium = 'National Stadium, Warsaw'

Более сложные задания

Кто забивал Германии?

В примере запроса показаны все голы, забитые в четвертьфинальном матче Германия (Germany) — Греция (Greece).

Вместо этого выведите имена всех игроков, забивших гол сборной Германии (Germany).

Выберите только голы, забитые игроками не из сборной Германии (Germany), в матчах, где GER указан как идентификатор team1 или team2.

Условие teamid!='GER' позволяет исключить игроков сборной Германии (Germany) из результата.

С помощью 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'

Общее количество голов

Выведите teamname и общее количество забитых голов.

В строке SELECT следует использовать COUNT(*), а затем сгруппировать результат по teamname с помощью GROUP BY

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

Голы по стадионам

Выведите stadium и количество голов, забитых на каждом стадионе.

SELECT stadium,COUNT(1)
  FROM goal JOIN game ON id=matchid
GROUP BY stadium

Матчи Польши

Для каждого матча с участием сборной Польши (Poland), обозначенной кодом 'POL', выведите matchid, дату и количество забитых голов.

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

Голы Германии

Для каждого матча, в котором сборная Германии (Germany), обозначенная кодом 'GER', забила гол, выведите matchid, дату матча и количество голов, забитых сборной Германии.

SELECT matchid,mdate,COUNT(teamid)
  FROM game JOIN goal ON matchid = id 
 WHERE (teamid='GER')
GROUP BY matchid,mdate

Все результаты

Выведите все матчи с участием сборной Англии (England), обозначенной кодом ENG, и количество голов, забитых каждой командой, как показано ниже. В этом задании используется конструкция "CASE WHEN", которая не рассматривалась в предыдущих упражнениях.

mdate	team1	score1	team2	score2
11 June 2012	FRA	1	ENG	1
15 June 2012	SWE	2	ENG	3
19 June 2012	ENG	1	UKR	0
24 June 2012	ENG	0	ITA	0

Обратите внимание: в приведённом запросе выводится каждый гол. Если гол забила команда team1, в столбце score1 выводится 1, в противном случае — 0. С помощью SUM можно получить количество голов, забитых командой team1. Отсортируйте результат по mdate, matchid, team1 и 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 
WHERE 'ENG' IN (team1, team2)
GROUP BY mdate,matchid,team1,team2

Что дальше?

JOIN Quiz

Old JOIN Tutorial

More JOIN operations: The next tutorial about the Movie database involves some slightly more complicated joins from the movie database.