Mysql подзапросы: Подзапросы в основных командах SQL

mysql — Передача данных внешнего запроса в подзапрос

Задать вопрос

Вопрос задан 4 года 1 месяц назад

Изменён 4 года назад

Просмотрен 1k раз

Нужно получить обновления и количество пользователей, интересующихся обновлениями. Для этого надо в подзапрос передать a.id, но при попытке выдается ошибка:

Неизвестный столбец ‘id’ в ‘where clause’

Структура БД:

announcements: id          |  title 
favorites: announcement_id | user_id 

Запрос:

SELECT a.title, a.id, f.rating 
FROM announcements as a, 
     (select announcement_id, count(*) as rating 
      from favorites 
      where favorites. announcement_id = a.id
     ) as f

Как правильно передать данные в подзарос? И если это невозможно, то как обойти проблему?

PS Использую phpmyadmin, СУБД mysql версии 10.1.38-MariaDB, движок innodb.

• mysql
• sql
• select
• mariadb
• подзапрос

В MySQL кореляционныe подзапросы не поддерживаются в FROM выражении. Перенесите подзапрос в SELECT лист:

select a.title, a.id, (
    select count(*) 
    from favorites as f 
    where f.announcement_id = a.id
    group by announcement_id) as rating 
from announcements a
;   

Ряд производителей БД, например, PostgreSQL или Oracle, поддерживают введённый в SQL:1999 стандарте так называемый

lateral derived tables синтаксис.
Тогда запрос будет выглядеть так:

select a.title, a.id, f.rating 
from announcements a, lateral (
    select announcement_id, count(*) as rating 
    from favorites f 
    where f.announcement_id = a.id
    group by announcement_id
) f
;     

8

Зарегистрируйтесь или войдите

Регистрация через Google

Регистрация через Facebook

Регистрация через почту

Отправить без регистрации

Почта

Необходима, но никому не показывается

Отправить без регистрации

Почта

Необходима, но никому не показывается

By clicking “Отправить ответ”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Подзапрос в условии ON для LEFT JOIN’а в MySQL — Хабр Q&A

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

SELECT U.*, S.*
FROM `user` AS U
LEFT JOIN stats AS S 
  ON S.user_id = u.ID 
AND s.date = (
SELECT max(date) 
  FROM stats AS S2 
  WHERE s2.user_id = u.id
)

WHERE U.id =1 — по желанию 🙂

Ответ написан более трёх лет назад

Комментировать

Простите, может я не очень понял.
Но по-моему, можно как-то проще:

SELECT user.ud, MAX(stats.date) FROM user LEFT JOIN stats ON stats.user_id = user.id 

не проверял, честно говорю.

Ответ написан 2012, в 21:55″> более трёх лет назад

Я бы лучше сделал SELECT user_id, MAX(date) FROM stats GROUP BY user_id, а потом бы приджойнил юзеров запросом из приложения вроде SELECT * FROM users WHERE user_id IN (…). Что-то я сомневаюсь, что четырехэтажные запросы с подзапросами будут быстро работать, да еще и в MySQL.

Ответ написан

более трёх лет назад

Комментировать

Не работает ваш запрос потому, что вы из подзапроса обратно выбираете тот же самый user_id, который «подали вовнутрь». Иными словами, ваш запрос идентичен следующему:

SELECT U.*, S.*
FROM `user` AS U
LEFT JOIN stats AS S ON S.user_id = U.id
WHERE U.id = 1

Как правильно сделать, уже написали выше. ИМХО, вариант с группировкой будет лучше всего, и читабельнее тоже. Хотя лучше сравните производительность на реальных данных.

На всякий случай, держите сам запрос:

SELECT u.*, MAX(s.date) max_date
FROM user u
LEFT JOIN stats s
  ON s.user_id = u.id
WHERE u.id = 1
GROUP BY u.id

Ответ написан более трёх лет назад

А если LIMIT убрат, он со всеми записями соединит? В SQLite, например, можно использовать внутренний идентификатор ряда без лимита, т. е. это было бы:

SELECT u.*, s.*
  FROM user u
  JOIN stat s ON s.rowid = (
       SELECT t.rowid
         FROM stat t
        WHERE t.user_id = u.id
     ORDER BY t.date DESC)
 WHERE u.id = ?

но там явно оговаривается, что (SELECT y ...) в выражении возвращает первую совпавшую запись, а не все; не знаю, как с этим в MySQL.

Ответ написан 2012, в 22:36″> более трёх лет назад

Если таблица stats представляет собой slowly changing dimensions и перформанс запроса на получения актуальной статистики важен, я бы подумал о других подходах как например денормализовать stats и хранить актуальные значения статистики в отдельной таблице (или даже у юзера), stats же пользовать как аудит изменений.

Ответ написан более трёх лет назад

Если не нравится паттерн с созданием аттрибутов статистики в сущности клиента, попробуйте тогда, в самом простом случае, держать одну таблицу для актуальных значений статистики с PK [user_id, stat_type] + таблицу с историческими значениями (аудит таблицу), которую будете сопровождать на триггерах, в случае изменении первой.

В еще более хитром случае, эти таблицы можно и объединить (как у вас сейчас — изначально), но для ускорения запросов добавить флаг current, который будет либо 1 либо NULL + составной индекс [current, user_id].

Геммор триггеров из предыдущего варианта уйдет, заменит его геммор сопровождения флага current.
Всевозможные варианты организации подобных исторических справочников с «+» и «-» описаны в википедийной статье на тему «slowly changing dimensions».

Задайте себе вопрос, кроме багтрейсов и редкой аналитики, Вам действительно принципиально гонять запросы на получение и исторических и актуальных данных по одной таблице? Если нет — первый вариант самое то + партицирование и удаление самых старых партиций на шедулере (по вкусу).

Ответ написан более трёх лет назад

Подзапрос MySQL — темы масштабирования

Обзор

Подзапрос в MySQL — это не что иное, как запрос внутри другого запроса. Подзапрос или вложенный запрос или внутренний запрос — это функция, при которой один SQL-запрос структурирован или написан внутри другого запроса. Подзапрос в MySQL разбивает сложные запросы или проблемы на более управляемые или более простые. Они разбивают сложные проблемы на более мелкие, что способствует гибкости запросов. Подзапрос в MySQL предлагает универсальность для выполнения широкого круга задач, таких как агрегирование данных, фильтрация данных на основе условий, извлечение данных и т. д.

В этой статье мы подробно изучим и поймем подзапросы в MySQL с реализованными примерами, где это необходимо.

Введение

MySQL — это РСУБД (система управления реляционными базами данных) с открытым исходным кодом, используемая для хранения и извлечения реляционных данных с помощью SQL (язык структурированных запросов). Они широко используются для создания надежных баз данных, в которых данные взаимосвязаны.

Крайне важно эффективно извлекать и обрабатывать реляционные данные в реляционных базах данных. Таким образом, эффективное управление данными может осуществляться с помощью гибкой и мощной функции MySQL, известной как Подзапросы .

Подзапрос или вложенный запрос или внутренний запрос — это функция, в которой один SQL-запрос структурирован или написан внутри другого запроса. Всякий раз, когда полный запрос записывается с внутренним запросом, внутренний запрос будет выполняться первым, а его выходные данные будут действовать как входные данные для внешнего запроса.

Операторы сравнения, такие как <, >, =, <=> и т. д., используются с подзапросами в MySQL для возврата либо истинного, либо ложного путем сравнения значений. Эти операторы сравнения сравнивают результат, возвращаемый внутренним запросом или подзапросом, с результатом, возвращаемым основным запросом.

Обычно подзапрос в MySQL разрешается писать справа от оператора сравнения. Их можно записывать как слева, так и справа от основного запроса.

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

Подзапросы в MySQL можно разделить на два типа:

• Коррелированные подзапросы
• Некоррелированные подзапросы

Коррелированные подзапросы в MySQL — это те типы подзапросов, которые оцениваются для каждой строки внешнего запроса. Коррелированные подзапросы зависят от внешнего запроса для их выполнения. Напротив,

Некоррелированные подзапросы — это те типы подзапросов, которые оцениваются один раз для создания вывода, используемого внешним запросом. Некоррелированные подзапросы независимы, их выполнение не зависит от внешнего запроса.

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

Синтаксис подзапроса MySQL

Подзапрос в MySQL имеет простой и удобный синтаксис для создания внутренних запросов или подзапросов.

Подзапросы создаются путем заключения внутреннего запроса или подзапроса в круглые скобки, которые в дальнейшем могут использоваться внешним запросом в качестве входных данных. Подзапросы в MySQL могут быть внедрены в определенные части, такие как предложение WHERE, оператор SELECT, предложение HAVING или предложение FROM основного запроса.

Давайте посмотрим на синтаксис, используемый для написания подзапроса в MySQL:

Пример подзапроса MySQL

После того, как мы многое поняли о введении и значении подзапросов в MySQL, давайте теперь перейдем к практическому пониманию подзапросов с помощью примера.

Пример

В этом примере мы рассмотрим таблицу product_info, содержащую такую ​​информацию, как product_id, product_name и цена. Мы получим информацию о продуктах, цена которых выше, чем средняя цена всех продуктов, представленных в таблице. Здесь мы будем использовать подзапрос для сравнения цены каждого продукта со средней ценой продуктов в таблице.

Рассмотрим таблицу product_info

product_id	product_name	цена
101	Наушники	15000
201	Телевидение	50000
301	Камера	45000
401	Мобильный телефон	40000
501	Умные часы	10000

Запрос:

Вывод

90 092 301

product_id	product_name
201	Телевидение
Камера
401	Мобильный телефон

Пояснение

900 04 В приведенном выше примере мы структурировали SQL-запрос, содержащий как основной, так и подзапрос. Так как мы знаем, что часть подзапроса будет выполнена и затем она будет использоваться основным запросом в качестве входных данных.

Здесь подзапрос используется для расчета средней цены всех продуктов в таблице. Основной запрос выбирает product_id и products_name из таблицы product_info. Предложение WHERE указывает условие, на основании которого в выходную таблицу будут включены товары, цена которых выше средней цены.

Подзапрос MySQL с оператором сравнения

Операторы сравнения, такие как <, >, =, <=> и т. д., используются с подзапросами в MySQL для возврата либо истинного, либо ложного путем сравнения значений. Эти операторы сравнения сравнивают результат, возвращаемый внутренним запросом или подзапросом, с результатом, возвращаемым основным запросом. Подзапросы, которые возвращают одно значение в качестве вывода, могут быть размещены либо слева, либо справа от оператора сравнения.

Давайте рассмотрим пример, чтобы лучше понять использование подзапросов с операторами сравнения.

Пример

В этом примере мы рассмотрим таблицу employee, имеющую такую ​​информацию, как employee_id, employee_dept и emp_salary. Здесь будет получена информация о том сотруднике, чья зарплата является максимальной среди всех сотрудников в таблице, независимо от отдела.

Рассмотрим следующую таблицу сотрудников:

900 92 104

идентификатор_сотрудника	employee_dept	emp_salary
101	IT	60000
102	Кадры	50000
103	Финансы	55000
Маркетинг	40000
105	Продажи	45000

90 011

Запрос

Выход

employee_id	employee_dept	emp_salary
103	IT 9 0093	60000

Объяснение

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

Значение или результат, возвращаемый подзапросом, будет сравниваться со значениями в основном запросе с использованием оператора равенства =. Поэтому, когда весь запрос будет выполнен, он вернет информацию о том сотруднике, чья зарплата максимальна.

Подзапрос MySQL с оператором IN или NOT-IN

Операторы сравнения IN или NOT IN используются для фильтрации результатов в MySQL. Оператор IN используется для фильтрации или включения набора результатов, соответствующего результату подзапроса.

Оператор сравнения NOT IN используется для фильтрации или исключения строк из набора результатов. Используется для аннулирования условия.

Операторы IN и NOT IN должны использоваться с предложением WHERE.

Давайте разберемся с использованием обоих операторов отдельно с подзапросами.

Example(IN)

В этом примере мы найдем клиентов, которые проживают в определенном городе, рассмотрев таблицу customer, имеющую такую ​​информацию, как customer_id, customer_name, customer_city. Для этого мы будем использовать оператор IN с подзапросом, который будет фильтровать и включать набор результатов, соответствующий результату, полученному подзапросом.

Рассмотрим следующую таблицу клиентов

идентификатор_клиента	имя_клиента	город_клиента
101	Джон	Даллас
201	Дэвид	Бостон
301	Сарая	Миннеаполис 90 093
401	Тайлер	Даллас
501	Эмили	Сидней

Запрос

Выход

90 092 401

идентификатор_клиента	имя_клиента
101	Джон
Тайлер

Объяснение

В приведенном выше примере мы получили сведения о клиентах, проживающих в Далласе. Подзапрос возвращает customer_id клиентов, проживающих в Далласе. Выходной или результирующий набор, возвращаемый подзапросом, используется основным запросом. Внешний запрос возвращает customer_id и customer_city на основе сравнения, выполненного оператором IN с предложением WHERE.

Пример(НЕ В)

В этом примере мы рассмотрим ту же таблицу, которая использовалась в приведенном выше примере, чтобы узнать, кто где проживает, за исключением определенного города. Для этого мы будем использовать оператор NOT IN с подзапросом, который будет фильтровать и исключать набор результатов, соответствующий результату, полученному подзапросом.

Давайте рассмотрим ту же таблицу клиентов, которая использовалась в приведенном выше примере.

Запрос

Вывод

идентификатор_клиента	имя_клиента
201	Джон
301	Saraya
501	Emily

Explanation

не проживает в Далласе. Подзапрос возвращает customer_id клиентов, проживающих где угодно, кроме Далласа. Выходной или результирующий набор, возвращаемый подзапросом, используется основным запросом. Внешний запрос возвращает customer_id и customer_city на основе сравнения, выполненного оператором NOT IN с предложением WHERE, которое исключает совпадающий результат, как показано в выходной таблице.

Подзапрос MySQL в предложении FROM

Предложение FROM в MySQL используется для выбора таблицы, из которой извлекаются результаты. Здесь подзапрос, используемый в предложении FROM, вернет временную таблицу, которая используется внешним запросом в качестве входных данных, из которых он может получить информацию.

Таблица, возвращаемая подзапросом, используемым в предложении FROM, называется временной таблицей или производной таблицей.

Давайте разберемся с использованием подзапроса в предложении FROM на примере.

Пример

В этом примере мы узнаем максимальное и минимальное количество товаров в таблице покупок. Максимальное и минимальное числа будут рассчитаны с использованием агрегатных функций MAX и MIN соответственно.

Рассмотрим следующий табличный продукт:

product_id	product_name	количество
101	Наушники	15
201	Телевизор	5
301	Камера	45
401 9 0093	Мобильный телефон	40
501	Умные часы	10

90 005 Запрос

Вывод

MAX (PRODS)	мин (PRODS)
1	1

Объяснение

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

Как обсуждалось ранее, коррелированные подзапросы в MySQL — это те типы подзапросов, которые оцениваются для каждой строки внешнего запроса. Коррелированные подзапросы зависят от внешнего запроса для их выполнения.

Давайте подробно разберем концепцию коррелированных подзапросов на примере.

Пример

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

Рассмотрим следующую таблицу служащих

имя_сотрудника	отдел_сотрудника	emp_salary
Сарайя	IT
Тайлер	Финансы 90 093	65000
Дональд	Маркетинг	60000
Моррис	Финансы	55000
Альби	IT	80000
Марк	Продажи	45000 9009 3
Пол	Маркетинг	50000
Эмили	Продажи	75000

Запрос

Выход

900 92 Тайлер

имя_сотрудника	отдел_сотрудника
Сарая	IT
Финансы
Дональд	Маркетинг
Энили	Продажи

Объяснение

В приведенном выше примере мы получили информацию о сотрудниках, у которых зарплата выше средней зарплаты в соответствующих отделах. Подзапрос вернет среднюю заработную плату сотрудников соответствующих отделов.

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

Подзапросы MySQL с EXISTS или NOT EXISTS

Операторы EXISTS и NOT EXISTS являются булевыми операторами, которые возвращают либо true , либо false . Оператор EXISTS в MySQL проверяет, присутствуют ли определенные данные или нет. Если подзапрос возвращает какой-либо результирующий набор или какое-либо значение, то оператор возвращает true, в противном случае — false.

Наоборот, оператор NOT EXISTS работает противоположно оператору EXISTS, то есть выводит true , если подзапрос не возвращает никакого результата или значения и выводит false , если подзапрос возвращает любое значение.

Оба оператора должны использоваться с коррелированными подзапросами .

Давайте разберемся, СУЩЕСТВУЕТ и НЕ СУЩЕСТВУЕТ на примере.

Example(EXISTS)

В этом примере мы рассмотрим две таблицы: одна — таблица сотрудников, а другая — таблица продуктов. Здесь мы получим информацию о тех сотрудниках, у которых есть хотя бы один заказ, используя оператор EXIST.

Рассмотрим обе таблицы Сотрудник и Продукты

Сотрудник

идентификатор_сотрудника	имя_сотрудника	отдел_сотрудника
101	Сарая 900 93	IT
102	Тайлер	Финансы
103	Эмили	Продажи 900 93
104	Марк	Маркетинг
105	Пол	HR

Продукция

product_name	employee_id	product_id
Холодильник	101 900 93	1
Телевизор	103	2
Ноутбук	104	3 90 093
Камера	105	4

Запрос

Выход

9 0092 103

идентификатор_сотрудника	имя_сотрудника
101	Сарая
Эмили
104	Марк
105	Пол

Объяснение

В приведенном выше примере мы проверили, есть ли у сотрудника хотя бы одна покупка или заказ, и на основании этого была получена информация о сотрудниках. В подзапросе он возвращает информацию о продукте вместе с employee_id тех сотрудников, которые присутствуют в обеих таблицах. Оператор EXISTS проверит, купил ли какой-либо сотрудник какой-либо продукт или нет, и если они купили, то оператор правда . После чего внешний запрос извлекает информацию о тех сотрудниках, у которых есть хотя бы одна покупка.

Пример (НЕ СУЩЕСТВУЕТ)

В этом примере мы рассмотрим две таблицы: одна — таблица сотрудников, а другая — таблица продуктов. Здесь мы получим информацию о тех сотрудниках, у которых нет покупок, используя оператор NOT EXIST.

Рассмотрим те же таблицы, что и в приведенном выше примере.

Запрос

Выход

9 0132

Объяснение

В приведенном выше примере мы получили информацию о сотрудниках, у которых нет покупок. В подзапросе он возвращает информацию о продукте вместе с employee_id тех сотрудников, которые присутствуют в обеих таблицах. Оператор НЕ СУЩЕСТВУЕТ проверит, купил ли какой-либо сотрудник какой-либо продукт или нет, и если они ничего не купили, то оператор вернет правда . После чего внешний запрос извлекает информацию о сотрудниках, у которых нет покупок.

Подзапросы ROW MySQL

Подзапросы строк — это подзапросы такого типа, которые возвращают одно значение или строку и более одного значения столбца. Подзапрос строки в MySQL можно сравнивать с помощью различных операторов сравнения, таких как >=, <=, =, !=.

Давайте разберемся с подзапросами ROW на примере.

Пример

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

Рассмотрим следующие столовые продукты:

идентификатор_сотрудника	имя_сотрудника
102	Тайлер

90 088

product_id	product_name	категория	product_price
101 9009 3	Наушники	Носимые устройства	5000
201	Телевидение	Электроника	20000
301	Браслет	Аксессуары	2000
401	Мобильный телефон	Электроника	10000
501	Солнцезащитные очки	Аксессуары 9009 3	4000
601	Умные часы	носимые устройства	8000

Запрос

Вывод

product_name	категория
Наушники	Носимые устройства
Браслет	Аксессуары
Мобильный телефон	Электроника

90 011

Объяснение

В приведенном выше примере мы получили информацию о продукте с самой низкой ценой в соответствующей категории. Для этого внутри подзапроса мы использовали агрегатную функцию MIN(), чтобы найти минимальную цену, и предложение GROUP BY, чтобы сгруппировать их по категориям. После предложения WHERE мы использовали ключевое слово ROW для сравнения комбинации строк за один раз, то есть (цена_продукта, категория). Внешний запрос сравнивает и показывает такую ​​информацию, как product_name и category, в выходном наборе результатов.

Подзапросы MySQL с ALL, ANY и SOME

ALL, ANY и SOME — это ключевые слова, которые используются после оператора сравнения в запросе. В MySQL ключевое слово ALL используется для сравнения результатов или значений со значениями, возвращаемыми подзапросами. Если сравнение всех значений истинно, то оно возвращает истинное значение.

Ключевое слово ANY используется для сравнения значений с любым значением, возвращаемым подзапросом. Если сравнение верно для любого значения, возвращаемого подзапросами, то ANY возвращает значение true.

Ключевое слово НЕКОТОРОЕ работает так же, как ключевое слово ЛЮБОЕ. Он также возвращает true, если какое-либо сравнение верно.

Заключение

Подытожим все, что мы узнали до сих пор:

• Подзапрос в MySQL — это не что иное, как запрос внутри другого запроса.
• Подзапрос или вложенный запрос или внутренний запрос — это функция, в которой один SQL-запрос структурирован или написан внутри другого запроса.
• Обычно подзапрос в MySQL разрешается писать справа от оператора сравнения.
• Подзапрос в MySQL разбивает сложные запросы или проблемы на более управляемые или более простые.
• Подзапросы в MySQL можно разделить на два типа:
  • Коррелированные подзапросы
  • Некоррелированные подзапросы
• Выполнение коррелированных подзапросов зависит от внешнего запроса.
• Некоррелированные подзапросы являются независимыми.

Как и когда писать подзапросы MySQL | Марко Санчес-Аяла

Реализация в операторах FROM и WHERE

Marco Sanchez-Ayala

·

Follow

Опубликовано в

·

4 мин чтения

900 04 ·

23 февраля 2020 г.

image from sqlservertutorial.net

Мой друг JP недавно опубликовал отличный пост с описанием основных функций MySQL для конвейеров ETL, в конце которого слегка затрагивается тема подзапросов. Я хотел бы остановиться на подзапросах и дать несколько советов и приемов, учитывая, насколько они могут быть полезны при выполнении многошаговых операций.

Подзапросы — это вложенные запросы, которые позволяют пользователю получать данные, требующие нескольких операций. Например, если у нас есть таблица, содержащая информацию о продуктах, мы можем захотеть найти максимальную цену для различных категорий продуктов, а затем взять среднее значение этих цен для каждой категории. Для этого нам нужно выполнить каждую агрегацию отдельно. Давайте посмотрим, как это работает на образце базы данных production :

Production databse схема 9.0004 Мы можем вызывать подзапрос в нескольких местах внутри запроса, но мы просто посмотрим, как использовать их в операторах FROM и WHERE , чтобы понять, как это работает.

Подзапрос в операторе FROM

Мы можем вставить подзапрос после оператора FROM для выбора столбцов и агрегирования результатов отдельного запроса! Давайте начнем с простого примера, чтобы понять синтаксис.

Пример 1

Синтаксис здесь довольно прост. После FROM , мы помещаем в круглые скобки наш подзапрос, который следует точно такому же синтаксису, как и любой обычный запрос. Вне конечных круглых скобок я назначаю псевдоним sub в строке 10, потому что, согласно MySQL, «каждая производная таблица должна иметь свой собственный псевдоним».

Проверив только подзапрос в 4–9, мы видим, что мы просто выбрали все столбцы, где model_year — это 2018. Затем внешний запрос просто возвращает все столбцы в подзапросе. В конечном счете, мы знаем, что тот же результат можно получить и без подзапроса, но я представил его таким образом, чтобы проиллюстрировать синтаксис.

Давайте рассмотрим немного более сложный пример. Допустим, мы хотим узнать среднее количество продуктов, выпускаемых в год. Мы создадим следующий запрос:

Пример 2

Чтобы понять, что происходит, давайте сначала посмотрим на подзапрос в строках 4–10. Этот запрос возвращает количество продуктов, производимых каждый год, путем агрегирования model_year . Этот результат подзапроса:

 +--------------------+ 
 | считать | модель_год | 
 |--------------------| 
 | 26 | 2016 | 
 | 85 | 2017 | 
 | 204 | 2018 | 
 | 6 | 2019 | 
 +--------------------+ 

Затем внешний запрос вычисляет среднее значение столбца counts , приведенного выше, равное 80,25. Хороший!

Теперь, в качестве более сложного примера, я покажу, как можно использовать оконную функцию внутри подзапроса. В этом примере мы хотим узнать 3 самых дорогих продукта для каждого brand_id в дополнение к информации о таких продуктах, как product_id , product_name , и т. д.

Пример 3

Оконная функция в строке 9 делает почти то же самое, что и говорит: она упорядочивает по list_price по убыванию для каждого brand_id и создает новый столбец, price_rank 911 44, который присваивает целочисленный ранг каждой строке. Для получения дополнительной информации об оконных функциях ознакомьтесь с этим замечательным руководством.

Получив нашу таблицу из подзапроса в строках 4–12, мы выбираем все столбцы, где price_rank< 4 , что ограничивает нас тремя самыми дорогими продуктами для каждого идентификатора бренда.

Выше мы видели, как реализовать подзапросы в операторе FROM , и что иногда вам даже не нужен подзапрос. То, что вы можете сделать , не всегда означает, что вы должны делать ! Давайте теперь рассмотрим другое применение подзапросов, которое, на мой взгляд, немного более полезно.

Подзапросы в операторе WHERE

Мы также можем реализовать подзапросы в условной логике. Что, если нас спросят о средней цене продуктов всего двух возможных торговых марок?

Пример 4

Мы создаем подзапрос (строки 6–13), который изолирует идентификаторы брендов для рассматриваемых торговых марок. Затем мы пишем внешний запрос, который находит среднее значение всех цен с идентификаторами брендов, совпадающими с ценами из результата подзапроса. Довольно аккуратно!

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

Давайте сделаем еще один шаг вперед… Можем ли мы получить название и цену любого товара, который стоит так же дорого или дешевле, чем средняя цена продуктов «Страйдер» и «Трек»?

Пример 5

Ответ — да, и все, что я сделал, — это обернул пример 4 еще одним запросом, фильтрующим по list_price .

В заключение

Существует множество способов написания полезных подзапросов в MySQL, включая другие способы, которые здесь не обсуждаются, такие как объединение подзапросов.

No related posts.