SQL: однорядные подзапросы

Последнее обновление 15 марта 2019 07:13:58 (UTC / GMT +8 часов)

script1adsense2code

script1adsense3code

Подзапросы с одной строкой

Одна строки подзапрос возвращает ноль или один ряд с внешним оператором SQL. Вы можете поместить подзапрос в предложение WHERE, предложение HAVING или предложение FROM оператора SELECT.

Содержание:

Подзапросы с одной строкой в предложении WHERE
Использование операторов сравнения в подзапросах Single Row
Подзапросы в предложении HAVING
Подзапросы в предложении FROM
Ошибка в однорядных подзапросах

Подзапросы с одной строкой в предложении WHERE

Вы можете разместить подзапрос в предложении WHERE другого запроса. Давайте рассмотрим пример запроса, который содержит подзапрос, помещенный в его предложение WHERE.

Пример таблицы: агенты

Код SQL:

SELECT agent_name, agent_code, phone_no
FROM agents
WHERE agent_code = 
(SELECT agent_code 
FROM agents
WHERE agent_name ='Alex');

Выход:

 AGENT_NAME AGENT_CODE PHONE_NO
---------------------------------------- ---------- ---------------
Алекс А003 075-12458969

Приведенный выше пример извлекает ageent_name, agent_code, phone_no из таблицы операторов, чье имя_агента 'Alex'. Давайте разберем запрос на две части и проанализируем, что происходит.

Код SQL:

SELECT agent_code 
FROM agents
WHERE agent_name ='Alex';

Выход:

 AGENT_CODE
----------
A003

Вышеупомянутый подзапрос выполняется первым и возвращает agent_code для строки, имя агента которой равно 'Alex'. Код_агента для этой строки - «A003», который передается предложению where внешнего (основного) запроса.
Упрощенная форма приведенного выше кода:

Код SQL:

SELECT agent_name, agent_code, phone_no
FROM agents
WHERE agent_code ='A003';

Наглядная презентация:

Использование операторов сравнения в подзапросах Single Row

В предыдущем примере использовался оператор равенства (=) в предложении WHERE. Другие операторы сравнения, такие как <>,>, <, <=, могут использоваться с одним подзапросом. В следующем примере используется оператор «<» во внешнем запросе WHERE. Функция AVG () используется в подзапросе для получения средней суммы заказа, которая передается предложению WHERE внешнего запроса. Окончательный результат всего запроса - получить значения ord_num, ord_amount, ord_date, cust_code и agent_code со следующими условиями:

Условие во внешнем запросе :
таблица 'ord_amount' из 'orders' должна быть больше среднего значения таблицы 'ord_amount' из 'orders' с следующим условием: внутреннее соединение.

Условие во внутреннем запросе :
'ord_date' таблицы 'orders' должно быть '20 -APR-08 'для расчета среднего значения' ord_amount '.

Пример таблицы: заказы

Вот полный оператор SQL:

Код SQL:

SELECT ord_num,ord_amount,ord_date,cust_code, agent_code
FROM orders
WHERE ord_amount>
(SELECT AVG(ord_amount)
FROM orders
WHERE ord_date='20-APR-08');

Выход:

 ORD_NUM ORD_AMOUNT ORD_DATE CUST_CODE AGENT_CODE
--------- ---------- --------- ---------- ----------
   200114 3500 15-AUG-08 C00002 A008
   200119 4000 16-сен-08 C00007 A010
   200134 4200 25-SEP-08 C00004 A005
   200108 4000 15-FEB-08 C00008 A004
   200109 3500 30 июля 2010 года C00011 A010
   200101 3000 15-ИЮЛ-08 C00001 A008
   200128 3500 20 июля 2010 года C00009 A002
   200132 4000 15-AUG-08 C00013 A013
   200110 3000 15-АПР-08 C00019 A010
   200107 4500 30-авг-08 C00007 A010
   200113 4000 10-ИЮНЬ-08 C00022 A002

Давайте разберем запрос и проанализируем, что происходит во внутреннем запросе. Вот код внутреннего запроса:

Код SQL:

SELECT AVG(ord_amount)
FROM orders
WHERE ord_date='20-APR-08';

Выход:

 Среднее (ORD_AMOUNT)
---------------
           2500

Приведенный выше запрос возвращает среднее значение ord_amount 2500, которое используется в предложении WHERE внешнего запроса, показанного ранее.

Упрощенная форма приведенного выше кода:

Код SQL:

SELECT ord_num,ord_amount,ord_date,cust_code, agent_code
FROM orders
WHERE ord_amount>2500;

Наглядная презентация:

Подзапросы в предложении HAVING

Предложение HAVING используется для фильтрации групп строк. Вы можете разместить подзапрос в предложении HAVING во внешнем запросе. Это позволяет вам фильтровать группы строк на основе результата, возвращенного вашим подзапросом. В следующем примере используется подзапрос в предложении HAVING внешнего запроса. В этом примере извлекаются 'ord_amount, число agent_codes и agent_code' из таблицы заказов со следующими условиями:

- агент_код таблицы заказов должен прийти отчетливо.
- среднее значение ord_amount для каждой группы кода агента в таблице заказов должно быть равно среднему значению ord_amount таблицы заказов.
- агент_код таблицы заказов должен быть 'A008'.

Пример таблицы: заказы

Вот полный оператор SQL:

Код SQL:

SELECT AVG(ord_amount),COUNT(agent_code),agent_code
FROM orders 
GROUP BY agent_code
HAVING AVG(ord_amount)=
(SELECT AVG(ord_amount) 
FROM orders
WHERE agent_code='A008');

Выход:

 AVG (ORD_AMOUNT) COUNT (AGENT_CODE) AGENT_CODE
--------------- ----------------- ----------
           2500 2 A011
           2500 3 A008

Давайте разберем код и проанализируем, что происходит во внутреннем запросе. Вот код внутреннего запроса:

Код SQL:

SELECT AVG(ord_amount) 
FROM orders
WHERE agent_code='A008';

Выход:

 Среднее (ORD_AMOUNT)
---------------
           2500

Внутренняя часть вышеприведенного запроса возвращает среднее значение ord_amount 2500.

Упрощенная форма кода:

Код SQL:

SELECT AVG(ord_amount),COUNT(agent_code),agent_code
FROM orders 
GROUP BY agent_code
HAVING AVG(ord_amount)=2500;

Иллюстрированная презентация:

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

Вы можете разместить подзапрос в предложении FROM внешнего запроса. Эти типы подзапросов также известны в виде встроенных представлений, поскольку подзапрос предоставляет данные, встроенные в предложение FROM. В следующем примере извлекается item_id, чей item_id меньше 4.

Пример таблицы: продукты

Вот оператор SQL:

Код SQL:

SELECT item_id
FROM  
(SELECT item_id   
FROM FOODS    
WHERE item_id<4)

Выход:

 ITEM_ID
------
1
2
3

Подзапрос возвращает строки из таблицы продуктов, чей item_id меньше 4, во внешний запрос, который затем извлекает и отображает эти значения item_id. Что касается предложения FROM внешнего запроса, то вывод из подзапроса является просто еще одним источником данных.

Наглядная презентация:

Ошибка в однорядных подзапросах

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

Пример таблицы: продукты

Код SQL:

SELECT item_id, item_name 
FROM foods
WHERE item_id =   
(SELECT item_id 
FROM foods   
WHERE item_name LIKE '%a%');

Если вы выполните запрос в Oracle Database 10g Express Edition, вы получите следующую ошибку:

ORA-01427: подзапрос из одной строки возвращает более одной строки

Давайте разберем код и проанализируем, что происходит во внутреннем запросе. Вот код внутреннего запроса:

Код SQL:

SELECT item_id
 FROM foods 
WHERE item_name LIKE '%a%';

Выход:

 ITEM_ID
------
5
7

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

Наглядная презентация: