Я хотел бы узнать следующее:
Я планирую использовать это в своем (например, PHP) приложении, но не хочу выполнять несколько запросов к базе данных, какие у меня есть возможности получить данные из нескольких таблиц в одном запросе?
Примечание: Я пишу это, так как хотел бы иметь возможность ссылаться на хорошо написанное руководство по многочисленным вопросам, с которыми я постоянно сталкиваюсь в очереди PHP, чтобы я мог ссылаться на него для получения более подробной информации, когда я опубликую ответ.
Ответы охватывают следующее:
В этом ответе рассматриваются:
mysql> create table colors(id int(3) not null auto_increment primary key,
-> color varchar(15), paint varchar(10));
Query OK, 0 rows affected (0.01 sec)
mysql> show columns from colors;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| color | varchar(15) | YES | | NULL | |
| paint | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> insert into colors (color, paint) values ('Red', 'Metallic'),
-> ('Green', 'Gloss'), ('Blue', 'Metallic'),
-> ('White' 'Gloss'), ('Black' 'Gloss');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from colors;
+----+-------+----------+
| id | color | paint |
+----+-------+----------+
| 1 | Red | Metallic |
| 2 | Green | Gloss |
| 3 | Blue | Metallic |
| 4 | White | Gloss |
| 5 | Black | Gloss |
+----+-------+----------+
5 rows in set (0.00 sec)
Таблица марок определяет различные марки автомобилей, которые могут быть проданы на автобазаре.
mysql> create table brands (id int(3) not null auto_increment primary key,
-> brand varchar(15));
Query OK, 0 rows affected (0.01 sec)
mysql> show columns from brands;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| brand | varchar(15) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> insert into brands (brand) values ('Ford'), ('Toyota'),
-> ('Nissan'), ('Smart'), ('BMW');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from brands;
+----+--------+
| id | brand |
+----+--------+
| 1 | Ford |
| 2 | Toyota |
| 3 | Nissan |
| 4 | Smart |
| 5 | BMW |
+----+--------+
5 rows in set (0.00 sec)
Таблица моделей будет охватывать различные типы автомобилей, для этого будет проще использовать различные типы автомобилей, а не реальные модели автомобилей.
mysql> create table models (id int(3) not null auto_increment primary key,
-> model varchar(15));
Query OK, 0 rows affected (0.01 sec)
mysql> show columns from models;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| model | varchar(15) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> insert into models (model) values ('Sports'), ('Sedan'), ('4WD'), ('Luxury');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from models;
+----+--------+
| id | model |
+----+--------+
| 1 | Sports |
| 2 | Sedan |
| 3 | 4WD |
| 4 | Luxury |
+----+--------+
4 rows in set (0.00 sec)
И, наконец, чтобы связать все эти таблицы, таблица, которая связывает все вместе. Поле ID - это уникальный номер партии, используемый для идентификации автомобилей.
mysql> create table cars (id int(3) not null auto_increment primary key,
-> color int(3), brand int(3), model int(3));
Query OK, 0 rows affected (0.01 sec)
mysql> show columns from cars;
+-------+--------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| color | int(3) | YES | | NULL | |
| brand | int(3) | YES | | NULL | |
| model | int(3) | YES | | NULL | |
+-------+--------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> insert into cars (color, brand, model) values (1,2,1), (3,1,2), (5,3,1),
-> (4,4,2), (2,2,3), (3,5,4), (4,1,3), (2,2,1), (5,2,3), (4,5,1);
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> select * from cars;
+----+-------+-------+-------+
| id | color | brand | model |
+----+-------+-------+-------+
| 1 | 1 | 2 | 1 |
| 2 | 3 | 1 | 2 |
| 3 | 5 | 3 | 1 |
| 4 | 4 | 4 | 2 |
| 5 | 2 | 2 | 3 |
| 6 | 3 | 5 | 4 |
| 7 | 4 | 1 | 3 |
| 8 | 2 | 2 | 1 |
| 9 | 5 | 2 | 3 |
| 10 | 4 | 5 | 1 |
+----+-------+-------+-------+
10 rows in set (0.00 sec)
Это даст нам достаточно данных (я надеюсь), чтобы охватить приведенные ниже примеры различных типов объединений, а также даст достаточно данных, чтобы сделать их стоящими.
Итак, вникая в суть, босс хочет узнать Идентификаторы всех спортивных автомобилей, которые у него есть.
Это простое объединение двух таблиц. У нас есть таблица, которая идентифицирует модель, и таблица с имеющимся запасом. Как видите, данные в столбце model
таблицы cars
относятся к столбцу models
имеющейся у нас таблицы cars
. Теперь мы знаем, что таблица models имеет идентификатор 1
для Sports
, поэтому напишем соединение.
select
ID,
model
from
cars
join models
on model=ID
Итак, этот запрос выглядит неплохо, верно? Мы определили две таблицы, содержащие необходимую нам информацию, и используем соединение, которое правильно определяет, по каким столбцам соединять.
ERROR 1052 (23000): Column 'ID' in field list is ambiguous
О нет! Ошибка в нашем первом запросе! Да, и это слив. Видите ли, в запросе действительно есть нужные столбцы, но некоторые из них существуют в обеих таблицах, поэтому база данных путается, какой именно столбец мы имеем в виду и где. Есть два решения для решения этой проблемы. Первое решение очень простое: мы можем использовать tableName.columnName
, чтобы указать базе данных, что именно мы имеем в виду, например:
select
cars.ID,
models.model
from
cars
join models
on cars.model=models.ID
+----+--------+
| ID | model |
+----+--------+
| 1 | Sports |
| 3 | Sports |
| 8 | Sports |
| 10 | Sports |
| 2 | Sedan |
| 4 | Sedan |
| 5 | 4WD |
| 7 | 4WD |
| 9 | 4WD |
| 6 | Luxury |
+----+--------+
10 rows in set (0.00 sec)
Второй способ, вероятно, используется чаще и называется псевдонимом таблиц. Таблицы в этом примере имеют красивые и короткие простые имена, но набирать что-то вроде KPI_DAILY_SALES_BY_DEPARTMENT
, вероятно, быстро надоест, поэтому простым способом является присвоение таблице псевдонима, например:
select
a.ID,
b.model
from
cars a
join models b
on a.model=b.ID
Теперь вернемся к запросу. Как вы можете видеть, у нас есть необходимая информация, но у нас также есть информация, которая не была запрошена, поэтому нам нужно включить в запрос предложение where, чтобы получить только автомобили Sports, как было запрошено. Поскольку я предпочитаю метод псевдонимов таблиц, а не использование имен таблиц снова и снова, я буду придерживаться его и в дальнейшем.
Очевидно, что нам нужно добавить в запрос условие where. Мы можем определить автомобили Sports либо по ID=1
, либо по model='Sports'
. Поскольку ID является индексируемым и первичным ключом (и, как оказалось, менее типизированным), давайте используем его в нашем запросе.
select
a.ID,
b.model
from
cars a
join models b
on a.model=b.ID
where
b.ID=1
+----+--------+
| ID | model |
+----+--------+
| 1 | Sports |
| 3 | Sports |
| 8 | Sports |
| 10 | Sports |
+----+--------+
4 rows in set (0.00 sec)
Бинго! Босс счастлив. Конечно, будучи боссом и никогда не будучи довольным тем, что он попросил, он просматривает информацию, а затем говорит Я хочу еще и цвета.
Итак, у нас уже написана большая часть запроса, но нам нужно использовать третью таблицу - цвета. Итак, в нашей основной информационной таблице cars
хранится идентификатор цвета автомобиля, который связан с колонкой ID цвета. Таким образом, аналогично оригиналу, мы можем присоединить третью таблицу:
select
a.ID,
b.model
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
where
b.ID=1
+----+--------+
| ID | model |
+----+--------+
| 1 | Sports |
| 3 | Sports |
| 8 | Sports |
| 10 | Sports |
+----+--------+
4 rows in set (0.00 sec)
Черт, хотя таблица была правильно объединена и связанные столбцы были связаны, мы забыли вытащить фактическую информацию из новой таблицы, которую мы только что связали.
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
where
b.ID=1
+----+--------+-------+
| ID | model | color |
+----+--------+-------+
| 1 | Sports | Red |
| 8 | Sports | Green |
| 10 | Sports | White |
| 3 | Sports | Black |
+----+--------+-------+
4 rows in set (0.00 sec)
Итак, на этом мы на время избавились от начальника. Теперь объясним некоторые моменты более подробно. Как вы можете видеть, предложение from
в нашем операторе связывает нашу основную таблицу (я часто использую таблицу, содержащую информацию, а не таблицу поиска или измерения. Запрос будет работать так же хорошо, если таблицы поменять местами, но будет иметь меньше смысла, когда мы вернемся к этому запросу, чтобы прочитать его через несколько месяцев, поэтому часто лучше попытаться написать запрос, который будет хорошо и легко понять - изложите его интуитивно, используйте хорошие отступы, чтобы все было настолько ясно, насколько это возможно. Если вы будете учить других, постарайтесь привить эти характеристики их запросам - особенно если вы будете устранять неполадки.
Вполне возможно продолжать связывать все больше и больше таблиц таким образом.
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=1
Хотя я забыл указать таблицу, в которой мы могли бы захотеть соединить более одного столбца в операторе join
, вот пример. Если в таблице models
были модели, относящиеся к конкретной марке, и поэтому в ней также был столбец brand
, который связывался с таблицей brands
по полю ID
, то это можно было бы сделать следующим образом:
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
and b.brand=d.ID
where
b.ID=1
Как видите, приведенный выше запрос не только связывает объединенные таблицы с основной таблицей cars
, но и определяет соединения между уже объединенными таблицами. Если это не было сделано, результат будет называться cartesian join - что на языке dba означает "плохой". Картезианское соединение - это соединение, в котором возвращаются строки, потому что информация не указывает базе данных, как ограничить результаты, поэтому запрос возвращает все строки, которые соответствуют критериям.
Чтобы привести пример картезианского соединения, выполним следующий запрос:
select
a.ID,
b.model
from
cars a
join models b
+----+--------+
| ID | model |
+----+--------+
| 1 | Sports |
| 1 | Sedan |
| 1 | 4WD |
| 1 | Luxury |
| 2 | Sports |
| 2 | Sedan |
| 2 | 4WD |
| 2 | Luxury |
| 3 | Sports |
| 3 | Sedan |
| 3 | 4WD |
| 3 | Luxury |
| 4 | Sports |
| 4 | Sedan |
| 4 | 4WD |
| 4 | Luxury |
| 5 | Sports |
| 5 | Sedan |
| 5 | 4WD |
| 5 | Luxury |
| 6 | Sports |
| 6 | Sedan |
| 6 | 4WD |
| 6 | Luxury |
| 7 | Sports |
| 7 | Sedan |
| 7 | 4WD |
| 7 | Luxury |
| 8 | Sports |
| 8 | Sedan |
| 8 | 4WD |
| 8 | Luxury |
| 9 | Sports |
| 9 | Sedan |
| 9 | 4WD |
| 9 | Luxury |
| 10 | Sports |
| 10 | Sedan |
| 10 | 4WD |
| 10 | Luxury |
+----+--------+
40 rows in set (0.00 sec)
Боже, как это уродливо. Однако для базы данных это точно то, что было запрошено. В запросе мы просили ID
из cars
и model
из models
. Однако, поскольку мы не указали как соединить таблицы, база данных сопоставила каждую строку из первой таблицы с каждой строкой из второй таблицы.
Итак, босс вернулся, и ему снова нужна дополнительная информация. Я хочу получить тот же список, но включить в него полноприводные автомобили.
Однако это дает нам отличный повод рассмотреть два разных способа достижения этой цели. Мы можем добавить еще одно условие к условию where, например:
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=1
or b.ID=3
Хотя вышеприведенное условие будет работать отлично, давайте посмотрим на это по-другому, это отличный повод показать, как будет работать объединенный
запрос.
Мы знаем, что следующий запрос вернет все спортивные автомобили:
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=1
А следующий запрос вернет все полноприводные автомобили:
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=3
Поэтому, добавив между ними предложение union all
, результаты второго запроса будут добавлены к результатам первого.
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=1
union all
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=3
+----+--------+-------+
| ID | model | color |
+----+--------+-------+
| 1 | Sports | Red |
| 8 | Sports | Green |
| 10 | Sports | White |
| 3 | Sports | Black |
| 5 | 4WD | Green |
| 7 | 4WD | White |
| 9 | 4WD | Black |
+----+--------+-------+
7 rows in set (0.00 sec)
Как видите, сначала возвращаются результаты первого запроса, а затем результаты второго.
В этом примере, конечно, было бы гораздо проще просто использовать первый запрос, но объединенные
запросы могут быть очень полезны для конкретных случаев. Это отличный способ вернуть конкретные результаты из таблиц, которые нелегко объединить вместе, или, например, из совершенно несвязанных таблиц. Однако есть несколько правил, которым необходимо следовать.
union
и union all
. Запрос union
удаляет дубликаты, а union all
- нет. Это означает, что при использовании union
по сравнению с union all
есть небольшое снижение производительности, но результаты могут того стоить - я не буду рассуждать об этом в этой статье.
В связи с этим, возможно, стоит сделать несколько дополнительных замечаний.order by
, но вы больше не можете использовать псевдоним. В приведенном выше запросе добавление order by a.ID
приведет к ошибке - в результатах столбец будет называться ID
, а не a.ID
- несмотря на то, что в обоих запросах использовался один и тот же псевдоним.order by
, и он должен быть последним.
В следующих примерах я добавлю несколько дополнительных строк в наши таблицы.
Я добавил Holden
в таблицу брендов.
Я также добавил строку в cars
, которая имеет значение color
, равное 12
- у которой нет ссылки в таблице colors.
Хорошо, босс снова вернулся, выкрикивая запросы - Я хочу подсчет каждой марки, которую мы продаем, и количество автомобилей в ней! - Типично, мы только что добрались до интересного раздела нашей дискуссии, а босс хочет еще работы.
Итак, первое, что нам нужно сделать, это получить полный список возможных марок.select
a.brand
from
brands a
+--------+
| brand |
+--------+
| Ford |
| Toyota |
| Nissan |
| Smart |
| BMW |
| Holden |
+--------+
6 rows in set (0.00 sec)
Теперь, когда мы присоединим это к нашей таблице автомобилей, мы получим следующий результат:
select
a.brand
from
brands a
join cars b
on a.ID=b.brand
group by
a.brand
+--------+
| brand |
+--------+
| BMW |
| Ford |
| Nissan |
| Smart |
| Toyota |
+--------+
5 rows in set (0.00 sec)
Что, конечно, является проблемой - мы не видим никакого упоминания о прекрасной марке Holden
, которую я добавил.
Это происходит потому, что объединение ищет совпадающие строки в обеих таблицах. Поскольку в автомобилях нет данных типа Holden
, они не возвращаются. Здесь мы можем использовать внешнее
присоединение. Это вернет все результаты из одной таблицы, независимо от того, совпадают они с данными в другой таблице или нет:
select
a.brand
from
brands a
left outer join cars b
on a.ID=b.brand
group by
a.brand
+--------+
| brand |
+--------+
| BMW |
| Ford |
| Holden |
| Nissan |
| Smart |
| Toyota |
+--------+
6 rows in set (0.00 sec)
Теперь, когда у нас это есть, мы можем добавить прекрасную агрегатную функцию, чтобы получить подсчет и на время отвлечь начальника от наших дел.
select
a.brand,
count(b.id) as countOfBrand
from
brands a
left outer join cars b
on a.ID=b.brand
group by
a.brand
+--------+--------------+
| brand | countOfBrand |
+--------+--------------+
| BMW | 2 |
| Ford | 2 |
| Holden | 0 |
| Nissan | 1 |
| Smart | 1 |
| Toyota | 5 |
+--------+--------------+
6 rows in set (0.00 sec)
И с этим босс скрывается.
Теперь, чтобы объяснить это более подробно, внешние соединения могут быть типа left
или right
. Левый или правый тип определяет, какая таблица будет полностью включена. При левом внешнем объединении
будут включены все строки из таблицы слева, а при правом внешнем объединении
(вы угадали) все результаты из таблицы справа будут включены в результаты.
Некоторые базы данных допускают полное внешнее соединение
, которое возвращает результаты (независимо от того, совпадают они или нет) из обеих таблиц, но это поддерживается не во всех базах данных.
Теперь, я думаю, вы задаетесь вопросом, можно ли объединять типы соединений в запросе - и ответ - да, можно.
select
b.brand,
c.color,
count(a.id) as countOfBrand
from
cars a
right outer join brands b
on b.ID=a.brand
join colors c
on a.color=c.ID
group by
a.brand,
c.color
+--------+-------+--------------+
| brand | color | countOfBrand |
+--------+-------+--------------+
| Ford | Blue | 1 |
| Ford | White | 1 |
| Toyota | Black | 1 |
| Toyota | Green | 2 |
| Toyota | Red | 1 |
| Nissan | Black | 1 |
| Smart | White | 1 |
| BMW | Blue | 1 |
| BMW | White | 1 |
+--------+-------+--------------+
9 rows in set (0.00 sec)
Итак, почему же это не те результаты, которые ожидались? Потому что, хотя мы выбрали внешнее соединение автомобилей с марками, оно не было указано в соединении с цветами - поэтому это конкретное соединение вернет только те результаты, которые совпадают в обеих таблицах. Вот запрос, который будет работать для получения ожидаемых результатов:
select
a.brand,
c.color,
count(b.id) as countOfBrand
from
brands a
left outer join cars b
on a.ID=b.brand
left outer join colors c
on b.color=c.ID
group by
a.brand,
c.color
+--------+-------+--------------+
| brand | color | countOfBrand |
+--------+-------+--------------+
| BMW | Blue | 1 |
| BMW | White | 1 |
| Ford | Blue | 1 |
| Ford | White | 1 |
| Holden | NULL | 0 |
| Nissan | Black | 1 |
| Smart | White | 1 |
| Toyota | NULL | 1 |
| Toyota | Black | 1 |
| Toyota | Green | 2 |
| Toyota | Red | 1 |
+--------+-------+--------------+
11 rows in set (0.00 sec)
Как мы видим, в запросе есть два внешних соединения, и результаты приходят, как и ожидалось.
А как насчет других типов соединений, спросите вы? Как насчет пересечений?
Ну, не все базы данных поддерживают пересечение
, но практически все базы данных позволят вам создать пересечение через объединение (или, по крайней мере, хорошо структурированный оператор where).
Пересечение - это тип объединения, несколько похожий на объединение
, как описано выше, но разница в том, что оно только возвращает строки данных, которые идентичны (и я действительно имею в виду идентичны) между различными отдельными запросами, объединенными объединением. Будут возвращены только те строки, которые идентичны во всех отношениях.
Простой пример выглядит следующим образом:
select
*
from
colors
where
ID>2
intersect
select
*
from
colors
where
id<4
В то время как обычный запрос union
вернет все строки таблицы (первый запрос вернет все, что больше ID>2
, а второй все, что имеет ID<4
), что приведет к полному набору, запрос intersect вернет только строку, соответствующую id=3
, поскольку она удовлетворяет обоим критериям.
Если ваша база данных не поддерживает запрос intersect
, то вышеописанное можно легко реализовать с помощью следующего запроса:
select
a.ID,
a.color,
a.paint
from
colors a
join colors b
on a.ID=b.ID
where
a.ID>2
and b.ID<4
+----+-------+----------+
| ID | color | paint |
+----+-------+----------+
| 3 | Blue | Metallic |
+----+-------+----------+
1 row in set (0.00 sec)
Если вы хотите выполнить пересечение в двух различных таблицах, используя базу данных, которая по своей природе не поддерживает запрос пересечения, вам необходимо создать объединение по каждому столбцу таблиц.
Ок, я нашел этот пост очень интересный и я хотел бы поделиться некоторыми из моих знаний о создании запроса. Спасибо за это Fluffeh. Других, кто может прочитать это и может почувствовать, что я'м неправильно, это 101% свободно редактировать и критиковать мой ответ. (Честно говоря, я чувствую себя очень благодарен за исправление моей ошибки(с).)
Я'll быть проводка на некоторые часто задаваемые вопросы в теге поддержку.
С учетом этой схемы
CREATE TABLE MovieList
(
ID INT,
MovieName VARCHAR(25),
CONSTRAINT ml_pk PRIMARY KEY (ID),
CONSTRAINT ml_uq UNIQUE (MovieName)
);
INSERT INTO MovieList VALUES (1, 'American Pie');
INSERT INTO MovieList VALUES (2, 'The Notebook');
INSERT INTO MovieList VALUES (3, 'Discovery Channel: Africa');
INSERT INTO MovieList VALUES (4, 'Mr. Bean');
INSERT INTO MovieList VALUES (5, 'Expendables 2');
CREATE TABLE CategoryList
(
MovieID INT,
CategoryName VARCHAR(25),
CONSTRAINT cl_uq UNIQUE(MovieID, CategoryName),
CONSTRAINT cl_fk FOREIGN KEY (MovieID) REFERENCES MovieList(ID)
);
INSERT INTO CategoryList VALUES (1, 'Comedy');
INSERT INTO CategoryList VALUES (1, 'Romance');
INSERT INTO CategoryList VALUES (2, 'Romance');
INSERT INTO CategoryList VALUES (2, 'Drama');
INSERT INTO CategoryList VALUES (3, 'Documentary');
INSERT INTO CategoryList VALUES (4, 'Comedy');
INSERT INTO CategoryList VALUES (5, 'Comedy');
INSERT INTO CategoryList VALUES (5, 'Action');
Вопрос
Найти все фильмы, которые принадлежат хотя бы как комедия
и Романс
категории.
Решение
Этот вопрос может быть очень сложно иногда. Может показаться, что запрос такой будет ответ:-
SELECT DISTINCT a.MovieName
FROM MovieList a
INNER JOIN CategoryList b
ON a.ID = b.MovieID
WHERE b.CategoryName = 'Comedy' AND
b.CategoryName = 'Romance'
что является очень неправильным, потому что он производит отсутствие результата. Объяснение этого заключается в том, что есть только одно допустимое значение категория
на каждой строке. Например, первое условие возвращает правда, второе условие всегда ложно. Таким образом, используя и оператора, оба условия должны быть истинны; в противном случае, это будет ложь. Другой запрос,как это,
SELECT DISTINCT a.MovieName
FROM MovieList a
INNER JOIN CategoryList b
ON a.ID = b.MovieID
WHERE b.CategoryName IN ('Comedy','Romance')
а результат все равно неверный, потому что матчи в записи как минимум один матч на категория
. В реальное разрешение будет путем подсчета количества записей экземпляров в кино. Количество экземпляров должно соответствовать общему количеству значений поставлено в условии.
SELECT a.MovieName
FROM MovieList a
INNER JOIN CategoryList b
ON a.ID = b.MovieID
WHERE b.CategoryName IN ('Comedy','Romance')
GROUP BY a.MovieName
HAVING COUNT(*) = 2
Заданной схеме,
CREATE TABLE Software
(
ID INT,
SoftwareName VARCHAR(25),
Descriptions VARCHAR(150),
CONSTRAINT sw_pk PRIMARY KEY (ID),
CONSTRAINT sw_uq UNIQUE (SoftwareName)
);
INSERT INTO Software VALUES (1,'PaintMe','used for photo editing');
INSERT INTO Software VALUES (2,'World Map','contains map of different places of the world');
INSERT INTO Software VALUES (3,'Dictionary','contains description, synonym, antonym of the words');
CREATE TABLE VersionList
(
SoftwareID INT,
VersionNo INT,
DateReleased DATE,
CONSTRAINT sw_uq UNIQUE (SoftwareID, VersionNo),
CONSTRAINT sw_fk FOREIGN KEY (SOftwareID) REFERENCES Software(ID)
);
INSERT INTO VersionList VALUES (3, 2, '2009-12-01');
INSERT INTO VersionList VALUES (3, 1, '2009-11-01');
INSERT INTO VersionList VALUES (3, 3, '2010-01-01');
INSERT INTO VersionList VALUES (2, 2, '2010-12-01');
INSERT INTO VersionList VALUES (2, 1, '2009-12-01');
INSERT INTO VersionList VALUES (1, 3, '2011-12-01');
INSERT INTO VersionList VALUES (1, 2, '2010-12-01');
INSERT INTO VersionList VALUES (1, 1, '2009-12-01');
INSERT INTO VersionList VALUES (1, 4, '2012-12-01');
Вопрос
Найти последние версии каждого программного обеспечения. Отображение следующих столбцов: SoftwareName
,описание
,последнюю версию
(из колонки VersionNo),DateReleased
Решение
Некоторые разработчики SQL ошибочно использовать Макс()` агрегатную функцию. Они стремятся создать такой,
SELECT a.SoftwareName, a.Descriptions,
MAX(b.VersionNo) AS LatestVersion, b.DateReleased
FROM Software a
INNER JOIN VersionList b
ON a.ID = b.SoftwareID
GROUP BY a.ID
ORDER BY a.ID
(большинство СУБД генерирует синтаксическую ошибку, это из-за не указания некоторых неагрегированных столбцы группы
п.) В результате получается правильный последнюю версию на каждое программное обеспечение, но, очевидно,
DateReleasedневерны.
MySQL в` не'т поддерживать функции окна " и " общее табличное выражение, но как некоторые РСУБД делать уже. Решение этой проблемы заключается в создании подзапрос, который возвращает versionNo максимальных на каждой из программ и в дальнейшем быть зарегистрирован на другие таблицы.
SELECT a.SoftwareName, a.Descriptions,
b.LatestVersion, c.DateReleased
FROM Software a
INNER JOIN
(
SELECT SoftwareID, MAX(VersionNO) LatestVersion
FROM VersionList
GROUP BY SoftwareID
) b ON a.ID = b.SoftwareID
INNER JOIN VersionList c
ON c.SoftwareID = b.SoftwareID AND
c.VersionNO = b.LatestVersion
GROUP BY a.ID
ORDER BY a.ID
Так, что это было. Я'll быть проводки другую только насколько я помню любой другой часто задаваемые вопросы на тег поддержку. Спасибо за прочтение этой небольшой статьи. Я надеюсь, что вы по крайней мере получить еще немного знаний от этого.
Обновление 1
Даны Схемы
CREATE TABLE userList
(
ID INT,
NAME VARCHAR(20),
CONSTRAINT us_pk PRIMARY KEY (ID),
CONSTRAINT us_uq UNIQUE (NAME)
);
INSERT INTO userList VALUES (1, 'Fluffeh');
INSERT INTO userList VALUES (2, 'John Woo');
INSERT INTO userList VALUES (3, 'hims056');
CREATE TABLE CONVERSATION
(
ID INT,
FROM_ID INT,
TO_ID INT,
MESSAGE VARCHAR(250),
DeliveryDate DATE
);
INSERT INTO CONVERSATION VALUES (1, 1, 2, 'hi john', '2012-01-01');
INSERT INTO CONVERSATION VALUES (2, 2, 1, 'hello fluff', '2012-01-02');
INSERT INTO CONVERSATION VALUES (3, 1, 3, 'hey hims', '2012-01-03');
INSERT INTO CONVERSATION VALUES (4, 1, 3, 'please reply', '2012-01-04');
INSERT INTO CONVERSATION VALUES (5, 3, 1, 'how are you?', '2012-01-05');
INSERT INTO CONVERSATION VALUES (6, 3, 2, 'sample message!', '2012-01-05');
Вопрос
Найти последний разговор между двумя пользователями.
Решение
SELECT b.Name SenderName,
c.Name RecipientName,
a.Message,
a.DeliveryDate
FROM Conversation a
INNER JOIN userList b
ON a.From_ID = b.ID
INNER JOIN userList c
ON a.To_ID = c.ID
WHERE (LEAST(a.FROM_ID, a.TO_ID), GREATEST(a.FROM_ID, a.TO_ID), DeliveryDate)
IN
(
SELECT LEAST(FROM_ID, TO_ID) minFROM,
GREATEST(FROM_ID, TO_ID) maxTo,
MAX(DeliveryDate) maxDate
FROM Conversation
GROUP BY minFROM, maxTo
)
Итак, теперь снова ворвался босс - Я хочу получить список всех наших автомобилей с указанием марки и общее количество автомобилей этой марки. Это отличная возможность использовать следующий трюк в нашей сумке SQL - подзапрос. Если вы не знакомы с этим термином, то подзапрос - это запрос, который выполняется внутри другого запроса. Существует множество различных способов их использования. Для нашего запроса давайте сначала составим простой запрос, который будет перечислять каждый автомобиль и его марку:
select
a.ID,
b.brand
from
cars a
join brands b
on a.brand=b.ID
Теперь, если бы мы хотели просто получить количество автомобилей, отсортированных по маркам, мы, конечно, могли бы написать следующее:
select
b.brand,
count(a.ID) as countCars
from
cars a
join brands b
on a.brand=b.ID
group by
b.brand
+--------+-----------+
| brand | countCars |
+--------+-----------+
| BMW | 2 |
| Ford | 2 |
| Nissan | 1 |
| Smart | 1 |
| Toyota | 5 |
+--------+-----------+
Итак, мы должны быть в состоянии просто добавить функцию count к нашему исходному запросу, верно?
select
a.ID,
b.brand,
count(a.ID) as countCars
from
cars a
join brands b
on a.brand=b.ID
group by
a.ID,
b.brand
+----+--------+-----------+
| ID | brand | countCars |
+----+--------+-----------+
| 1 | Toyota | 1 |
| 2 | Ford | 1 |
| 3 | Nissan | 1 |
| 4 | Smart | 1 |
| 5 | Toyota | 1 |
| 6 | BMW | 1 |
| 7 | Ford | 1 |
| 8 | Toyota | 1 |
| 9 | Toyota | 1 |
| 10 | BMW | 1 |
| 11 | Toyota | 1 |
+----+--------+-----------+
11 rows in set (0.00 sec)
К сожалению, нет, мы не можем этого сделать. Причина в том, что когда мы добавляем идентификатор автомобиля (столбец a.ID), мы должны добавить его в группу по - поэтому теперь, когда работает функция подсчета, для каждого идентификатора будет найдено только одно совпадение.
Однако здесь мы можем использовать подзапрос - на самом деле мы можем сделать два совершенно разных типа подзапросов, которые вернут те же результаты, которые нам нужны. Первый - просто поместить подзапрос в предложение select
. Это означает, что каждый раз, когда мы получаем строку данных, подзапрос будет работать, получать столбец данных и затем вставлять его в нашу строку данных.
select
a.ID,
b.brand,
(
select
count(c.ID)
from
cars c
where
a.brand=c.brand
) as countCars
from
cars a
join brands b
on a.brand=b.ID
+----+--------+-----------+
| ID | brand | countCars |
+----+--------+-----------+
| 2 | Ford | 2 |
| 7 | Ford | 2 |
| 1 | Toyota | 5 |
| 5 | Toyota | 5 |
| 8 | Toyota | 5 |
| 9 | Toyota | 5 |
| 11 | Toyota | 5 |
| 3 | Nissan | 1 |
| 4 | Smart | 1 |
| 6 | BMW | 2 |
| 10 | BMW | 2 |
+----+--------+-----------+
11 rows in set (0.00 sec)
И Бам! Это нам поможет. Однако, если вы заметили, этот подзапрос будет выполняться для каждого отдельного ряда данных, которые мы возвращаем. Даже в этом маленьком примере у нас есть только пять различных марок автомобилей, но подзапрос выполняется одиннадцать раз, поскольку у нас есть одиннадцать строк данных, которые мы возвращаем. Таким образом, в данном случае это не самый эффективный способ написания кода. Для другого подхода давайте запустим подзапрос и представим, что это таблица:
select
a.ID,
b.brand,
d.countCars
from
cars a
join brands b
on a.brand=b.ID
join
(
select
c.brand,
count(c.ID) as countCars
from
cars c
group by
c.brand
) d
on a.brand=d.brand
+----+--------+-----------+
| ID | brand | countCars |
+----+--------+-----------+
| 1 | Toyota | 5 |
| 2 | Ford | 2 |
| 3 | Nissan | 1 |
| 4 | Smart | 1 |
| 5 | Toyota | 5 |
| 6 | BMW | 2 |
| 7 | Ford | 2 |
| 8 | Toyota | 5 |
| 9 | Toyota | 5 |
| 10 | BMW | 2 |
| 11 | Toyota | 5 |
+----+--------+-----------+
11 rows in set (0.00 sec)
Итак, у нас те же результаты (упорядоченные немного по-другому - похоже, база данных хотела вернуть результаты, упорядоченные по первому столбцу, который мы выбрали в этот раз) - но те же правильные числа.
Итак, в чем же разница между этими двумя типами запросов - и когда нам следует использовать каждый тип подзапроса? Во-первых, давайте убедимся, что мы понимаем, как работает второй запрос. Мы выбрали две таблицы в пункте from
нашего запроса, а затем написали запрос и сказали базе данных, что на самом деле это была таблица - что базу данных вполне устраивает. Использование этого метода может иметь некоторые преимущества (а также некоторые ограничения). Прежде всего, этот подзапрос выполняется один раз. Если бы наша база данных содержала большой объем данных, можно было бы добиться значительного улучшения по сравнению с первым методом. Однако, поскольку мы используем эту таблицу в качестве таблицы, нам приходится вводить дополнительные строки данных, чтобы их можно было присоединить к нашим строкам данных. Мы также должны быть уверены, что существует достаточно строк данных, если мы собираемся использовать простое объединение, как в запросе выше. Если вы помните, соединение будет возвращать только те строки, которые имеют совпадающие данные с обеих сторон соединения. Если мы не будем осторожны, это может привести к тому, что достоверные данные не будут возвращены из нашей таблицы автомобилей, если в этом подзапросе не было совпадающей строки.
Теперь, возвращаясь к первому подзапросу, можно отметить некоторые ограничения. Поскольку мы возвращаем данные в одну строку, мы можем вернуть только одну строку данных. Подзапросы, используемые в пункте select
запроса, очень часто используют только агрегатную функцию, такую как sum
, count
, max
или другую подобную агрегатную функцию. Это не обязательно, но часто именно так они и пишутся.
Итак, прежде чем двигаться дальше, давайте посмотрим, где еще мы можем использовать подзапрос. Мы можем использовать его в предложении where
- теперь, этот пример немного надуманный, поскольку в нашей базе данных есть лучшие способы получить следующие данные, но поскольку это только для примера, давайте посмотрим:
select
ID,
brand
from
brands
where
brand like '%o%'
+----+--------+
| ID | brand |
+----+--------+
| 1 | Ford |
| 2 | Toyota |
| 6 | Holden |
+----+--------+
3 rows in set (0.00 sec)
Это возвращает нам список идентификаторов брендов и названий брендов (второй столбец добавлен только для того, чтобы показать нам бренды), которые содержат букву o
в названии.
Теперь мы можем использовать результаты этого запроса в предложении where следующим образом:
select
a.ID,
b.brand
from
cars a
join brands b
on a.brand=b.ID
where
a.brand in
(
select
ID
from
brands
where
brand like '%o%'
)
+----+--------+
| ID | brand |
+----+--------+
| 2 | Ford |
| 7 | Ford |
| 1 | Toyota |
| 5 | Toyota |
| 8 | Toyota |
| 9 | Toyota |
| 11 | Toyota |
+----+--------+
7 rows in set (0.00 sec)
Как видите, несмотря на то, что подзапрос возвращает три идентификатора марки, в нашей таблице cars есть записи только для двух из них. В данном случае, если говорить более подробно, подзапрос работает так, как если бы мы написали следующий код:
select
a.ID,
b.brand
from
cars a
join brands b
on a.brand=b.ID
where
a.brand in (1,2,6)
+----+--------+
| ID | brand |
+----+--------+
| 1 | Toyota |
| 2 | Ford |
| 5 | Toyota |
| 7 | Ford |
| 8 | Toyota |
| 9 | Toyota |
| 11 | Toyota |
+----+--------+
7 rows in set (0.00 sec)
Опять же, вы можете видеть, как подзапрос по сравнению с ручным вводом изменил порядок строк при возврате из базы данных. Пока мы обсуждаем подзапросы, давайте посмотрим, что еще мы можем сделать с помощью подзапроса:
select
, несколько в предложение from
и еще пару в предложение where
- просто помните, что каждый такой подзапрос делает ваш запрос более сложным и, скорее всего, займет больше времени на выполнение.
Если вам нужно написать эффективный код, может быть полезно написать запрос несколькими способами и посмотреть (либо по времени, либо с помощью плана объяснения), какой из них является оптимальным для получения результатов. Первый способ, который работает, не всегда является лучшим.Я решил добавить несколько дополнительных кусочков, для советов и трюков, которые возникли.
Один из вопросов, который я вижу, возникает довольно часто, это Как получить несовпадающие строки из двух таблиц, и я вижу, что чаще всего ответ принимается примерно следующим образом (на основе нашей таблицы автомобилей и марок - в которой Holden указан как марка, но не появляется в таблице автомобилей):
select
a.ID,
a.brand
from
brands a
where
a.ID not in(select brand from cars)
И да это будет работать.
+----+--------+
| ID | brand |
+----+--------+
| 6 | Holden |
+----+--------+
1 row in set (0.00 sec)
Однако это не эффективно в некоторых базах данных. Вот ссылка на вопрос на Stack Overflow, где об этом спрашивают, а вот отличная подробная статья, если вы хотите вникнуть в суть.
Короткий ответ: если оптимизатор не справляется с этим эффективно, то для получения несовпадающих строк лучше использовать запрос, подобный следующему:
select
a.brand
from
brands a
left join cars b
on a.id=b.brand
where
b.brand is null
+--------+
| brand |
+--------+
| Holden |
+--------+
1 row in set (0.00 sec)
Аххх, еще одно старое, но доброе - старое Вы не можете указать целевую таблицу 'бренды' для обновления в предложении FROM.
MySQL не позволит вам выполнить запрос update...
с подвыбором по одной и той же таблице. Теперь вы можете подумать, почему бы просто не вставить его в предложение where? Но что если вы хотите обновить только строку с датой max()
среди множества других строк? Вы не можете сделать это в предложении where.
update
brands
set
brand='Holden'
where
id=
(select
id
from
brands
where
id=6);
ERROR 1093 (HY000): You can't specify target table 'brands'
for update in FROM clause
Итак, мы не можем этого сделать, да? Ну, не совсем. Существует хитрый обходной путь, о котором удивительно много пользователей не знают - хотя он включает в себя некоторые хакерские приемы, на которые вам придется обратить внимание.
Вы можете поместить подзапрос внутри другого подзапроса, что обеспечит достаточный промежуток между двумя запросами, и тогда он будет работать. Однако обратите внимание, что безопаснее всего поместить запрос в транзакцию - это предотвратит любые другие изменения, вносимые в таблицы во время выполнения запроса.
update
brands
set
brand='Holden'
where id=
(select
id
from
(select
id
from
brands
where
id=6
)
as updateTable);
Query OK, 0 rows affected (0.02 sec)
Rows matched: 1 Changed: 0 Warnings: 0
Вы можете использовать концепции множественных запросов с сайта. Позвольте мне показать вам один пример:
SELECT DISTINCT e.id,e.name,d.name,lap.lappy LAPTOP_MAKE,c_loc.cnty COUNTY
FROM (
SELECT c.id cnty,l.name
FROM county c, location l
WHERE c.id=l.county_id AND l.end_Date IS NOT NULL
) c_loc, emp e
INNER JOIN dept d ON e.deptno =d.id
LEFT JOIN
(
SELECT l.id lappy, c.name cmpy
FROM laptop l, company c
WHERE l.make = c.name
) lap ON e.cmpy_id=lap.cmpy
Вы можете использовать так много таблиц, как вы хотите. Использовать внешние соединения и объединения, где бы он's необходимый, даже внутри табличные подзапросы.
Что's очень легкий способ привлечь столько таблиц и полей.
Надеется, что это делает ее найдете столы как вы'повторного прочтения вещь:
[jsfiddle][1]
mysql> show columns from colors;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| color | varchar(15) | YES | | NULL | |
| paint | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+