Вывод списков для записей «главная-подчиненная» и итогов

Задача
Две таблицы соотносятся как «главная-подчиненная» (master-detail), и вы хотите вывести список, который для каждой главной записи выводит соответствующие ей подчиненные, или список, в котором просуммированы подчиненные записи для каждой главной.

Решение
Для решения задачи необходимо использовать соединение, тип которого зависит от конкретной задачи. Чтобы вывести список, содержащий только те главные записи, для которых есть какие-то подчиненные, применяйте обычное соединение на основе первичного ключа главной таблицы. Чтобы вывести список, который включает все главные записи, даже если для них нет подчиненных, используйте LEFT JOIN.

Обсуждение
Часто требуется сформировать список записей двух связанных таблиц. Если таблицы имеют связь «главная-подчиненная» (или связь «предок-потомок», parent-child), то указанная запись одной таблицы может соответствовать нескольким записям другой. Этот раздел описывает несколько таких вопросов, которые можно задать (и на которые можно ответить) для таблиц artist и painting.

Одним из вопросов типа «главная-подчиненная» для этих таблиц может быть такой: «Какой художник написал каждую из картин?».


Вот простое со-единение, сопоставляющее каждую запись таблицы painting соответствующей записи artist на основе значений идентификаторов художников:

mysql> SELECT artist.name, painting.title
-> FROM artist, painting WHERE artist.a_id = painting.a_id
-> ORDER BY 1, 2;

+------------+----------------------+
| name | title |
+------------+----------------------+
| Da Vinci | The Last Supper |
| Da Vinci | The Mona Lisa |
| Renoir | Les Deux Soeurs |
| Van Gogh | Starry Night |
| Van Gogh | The Potato Eaters |
| Van Gogh | The Rocks |
+------------+----------------------+

Такого соединения достаточно, пока вас интересуют только те главные записи, которым соответствуют подчиненные. Однако есть и другие вопросы типа «главная-подчиненная», например, «Какие картины написал каждый из авторов?». Этот вопрос похож на предыдущий, но не совсем идентичен ему.

И ответ на него будет другим, если есть художники, приведенные в таблице artist, но не представленные в таблице painting. Для вывода правильного ответа необходимо использовать не такой запрос, как в предыдущем примере.

В данном случае вывод соединения должен включать те записи одной таблицы, для которых не найдено соответствия в другой.


Это разновидность задачи нахождения записей без соответствий, поэтому для вывода всех записей artist, вне зависимости от того, существуют ли для них записи painting, используйте LEFT JOIN:

mysql> SELECT artist.name, painting.title
-> FROM artist LEFT JOIN painting ON artist.a_id = painting.a_id
-> ORDER BY 1, 2;

+------------+----------------------+
| name | title |
+------------+----------------------+
| Da Vinci | The Last Supper |
| Da Vinci | The Mona Lisa |
| Monet | NULL |
| Picasso | NULL |
| Renoir | Les Deux Soeurs |
| Van Gogh | Starry Night |
| Van Gogh | The Potato Eaters |
| Van Gogh | The Rocks |
+------------+----------------------+

Строки результирующего множества, которые содержат NULL в столбце title, соответствуют авторам из таблицы artist, картин которых у вас еще нет. Те же принципы применяются к формированию итогов с использованием главной и подчиненной таблиц. Например, чтобы сформировать такую итоговую информацию о вашей коллекции, как количество картин каждого ху-дожника, можно спросить: «Сколько картин каждого автора есть в таблице painting?».


Чтобы вывести в ответе идентификаторы художников, можно сосчитать картины, выполнив такой запрос:

mysql> SELECT a_id, COUNT(a_id) AS count FROM painting GROUP BY a_id;

+-----+-------+
| a_id | count |
+-----+-------+
| 1 | 2 |
| 3 | 3 |
| 5 | 1 |
+-----+-------+

Конечно, в таком выводе не очень много смысла, если только вы не помните, какому художнику соответствует каждый идентификатор. Для вывода фамилий художников вместо их идентификаторов объединим таблицу painting с таблицей artist:

mysql> SELECT artist.name AS painter, COUNT(painting.a_id) AS count
-> FROM artist, painting
-> WHERE artist.a_id = painting.a_id
-> GROUP BY artist.name;

+-----------+--------+
| painter | count |
+-----------+--------+
| Da Vinci | 2 |
| Renoir | 1 |
| Van Gogh | 3 |
+-----------+--------+

Можно сформулировать вопрос по-другому: «Сколько картин написал каждый художник?». Это такой же вопрос, как предыдущий, и ответ на него будет таким же, если каждому художнику из таблицы artist соответствует хотя бы одна запись таблицы painting.


Но если у вас есть авторы, которые еще не представлены никакими работами в вашей коллекции, то они не будут присутствовать в выводе запроса. Чтобы сформировать итоги и по тем художникам, чьих произведений нет в таблице painting, используйте LEFT JOIN:

mysql> SELECT artist.name AS painter, COUNT(painting.a_id) AS count
-> FROM artist LEFT JOIN painting ON artist.a_id = painting.a_id
-> GROUP BY artist.name;

+------------+-------+
| painter | count |
+------------+-------+
| Da Vinci | 2 |
| Monet | 0 |
| Picasso | 0 |
| Renoir | 1 |
| Van Gogh | 3 |
+------------+-------+

При создании запросов такого типа легко сделать одну малозаметную ошибку. Предположим, что вы написали запрос несколько иначе:

mysql> SELECT artist.name AS painter, COUNT(*) AS count
-> FROM artist LEFT JOIN painting ON artist.a_id = painting.a_id
-> GROUP BY artist.name;

+------------+-------+
| painter | count |
+-----------+--------+
| Da Vinci | 2 |
| Monet | 1 |
| Picasso | 1 |
| Renoir | 1 |
| Van Gogh | 3 |
+------------+-------+

Теперь у каждого автора есть хотя бы одна картина.


Почему? Причина проблемы в использовании COUNT(*) вместо COUNT(painting.a_id). Соединение LEFT JOIN работает со строками левой таблицы, которым не найдено соответствий, так: генерируется строка, в которой все столбцы правой таблицы установлены в NULL. В нашем примере правой таблицей является painting. Запрос, использующий COUNT(painting.a_id), работает корректно, так как COUNT(выражение) не учитывает значения NULL. Запрос, использующий COUNT(*), работает неправильно, так как он подсчитывает все значения, даже строки, соответствующие отсутствующим художникам.

LEFT JOIN подходит и для формирования других видов итогов. Чтобы вывести дополнительные столбцы, содержащие общую и среднюю стоимость картин каждого автора из таблицы artist, выполните такой запрос:

mysql> SELECT artist.name AS painter,
-> COUNT(painting.a_id) AS 'number of paintings',
-> SUM(painting.price) AS 'total price',
-> AVG(painting.price) AS 'average price'
-> FROM artist LEFT JOIN painting ON artist.a_id = painting.a_id
-> GROUP BY artist.name;

+-----------+-------------------------+-------------+----------------+
| painter | number of paintings | total price | average price |
+-----------+-------------------------+-------------+----------------+
| Da Vinci | 2 | 121 | 60.5000 |
| Monet | 0 | 0 | NULL |
| Picasso | 0 | 0 | NULL |
| Renoir | 1 | 64 | 64.0000 |
| Van Gogh | 3 | 148 | 49.3333 |
+-----------+-------------------------+-------------+----------------+

Обратите внимание на то, что COUNT() и SUM() равны нулю для художников, чьих картин у вас нет, но AVG() содержит NULL. Дело в том, что AVG() вычисляется как сумма, деленная на количество; если же количество равно нулю, то значение не определено. Чтобы вывести для этого случая среднее значение, равное нулю, измените запрос так, чтобы значение AVG() проверялось при помощи IFNULL():

mysql> SELECT artist.name AS painter,
-> COUNT(painting.a_id) AS 'number of paintings',-> SUM(painting.price) AS 'total price',
-> IFNULL(AVG(painting.price),0) AS 'average price'
-> FROM artist LEFT JOIN painting ON artist.a_id = painting.a_id
-> GROUP BY artist.name;

+-----------+-------------------------+-------------+----------------+
| painter | number of paintings | total price | average price |
+-----------+-------------------------+-------------+----------------+
| Da Vinci | 2 | 121 | 60.5000 |
| Monet | 0 | 0 | 0 |
| Picasso | 0 | 0 | 0 |
| Renoir | 1 | 64 | 64.0000 |
| Van Gogh | 3 | 148 | 49.3333 |
+------------+------------------------+-------------+----------------+

Оцените статью: (0 голосов)
0 5 0

Статьи из раздела MySQL на эту тему:
Вставка записей в таблицу, включающую значения из другой
Вычисление разности между последовательными строками
Вычисление рейтинга команд
Выявление и удаление несвязанных записей
Заполнение пустых мест в списке с помощью соединения