кодесурса
«SQL

Вложенные подзапросы SQL

script1adsense2code
script1adsense3code

Вложенные подзапросы

Подзапрос может быть вложен в другие подзапросы. SQL имеет возможность вкладывать запросы друг в друга. Подзапрос - это оператор SELECT, который вложен в другой оператор SELECT и возвращает промежуточные результаты. SQL сначала выполняет самый внутренний подзапрос, затем следующий уровень. Смотрите следующие примеры:

Пример -1: вложенные подзапросы

Если мы хотим извлечь этот уникальный идентификатор job_id и его среднюю зарплату из таблицы сотрудников, у которой уникальный job_id имеет зарплату меньше чем (максимальное из средних значений min_salary для каждого уникального job_id из таблицы заданий, для которой job_id есть в списке, выбирая из ( таблица job_history, которая находится в отделе_id 50 и 100)), можно использовать следующий оператор SQL:

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


Пример таблицы: вакансии


Код SQL: 

SELECT job_id,AVG(salary) 
FROM employees   
GROUP BY job_id   
HAVING AVG(salary)<           
(SELECT MAX(AVG(min_salary))            
FROM jobs             
WHERE job_id IN                 
(SELECT job_id FROM job_history                  
WHERE department_id                   
BETWEEN 50 AND 100)             
GROUP BY job_id);

Приведенный выше код выполняется в Oracle 11g Express Edition.

или же 

SELECT job_id,AVG(salary) 
SELECT job_id,AVG(salary) 
FROM employees   
GROUP BY job_id   
HAVING AVG(salary)<
(SELECT MAX(myavg) from (select job_id,AVG(min_salary) as myavg
FROM jobs             
WHERE job_id IN                 
(SELECT job_id FROM job_history                  
WHERE department_id                   
BETWEEN 50 AND 100)            
GROUP BY job_id) ss);

Приведенный выше код выполняется в PostgreSQL 9.3

Выход 

 JOB_ID AVG (SALARY)
---------- -----------
IT_PROG 5760
AC_ACCOUNT 8300
ST_MAN 7280
AD_ASST 4400
SH_CLERK 3215
FI_ACCOUNT 7920
PU_CLERK 2780
SA_REP 8350
MK_REP 6000
ST_CLERK 2785
HR_REP 6500

Объяснение:

Этот пример содержит три запроса: вложенный подзапрос, подзапрос и внешний запрос. Эти части запросов выполняются в указанном порядке.

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

Сначала вложенный подзапрос следующим образом:

Код SQL: 

SELECT job_id FROM job_history  
WHERE department_id      
BETWEEN 50 AND 100;

Этот вложенный подзапрос извлекает job_id (ы) из таблицы job_history, которая находится в отделе_id 50 и 100.

Вот вывод.

Выход: 

 JOB_ID
----------
ST_CLERK
ST_CLERK
IT_PROG
SA_REP
SA_MAN
AD_ASST
AC_ACCOUNT

Вот графическое представление того, как получается вышеприведенный вывод.

«Sql

Теперь подзапрос, который получает выходные данные из вложенного подзапроса, указанного выше. 


SELECT MAX(AVG(min_salary))
FROM jobs WHERE job_id 
IN(.....output from the nested subquery......)
GROUP BY job_id

Внутренний подзапрос работает следующим образом:

Код SQL: 

SELECT MAX(AVG(min_salary))
FROM jobs 
WHERE job_id 
IN(
'ST_CLERK','ST_CLERK','IT_PROG',	
'SA_REP','SA_MAN','AD_ASST',	'
AC_ACCOUNT')
GROUP BY job_id;

Подзапрос возвращает максимум средних значений min_salary для каждого уникального возврата job_id (т. Е. «ST_CLERK», «ST_CLERK», «IT_PROG», «SA_REP», «SA_MAN», «AD_ASST», «AC_ACCOUNT») по предыдущему подзапросу.

Вот вывод:

Выход: 

 MAX (AVG (MIN_SALARY))
--------------------
               10000

Вот графическое представление того, как возвращается вышеприведенный вывод.

«Sql

Теперь внешний запрос, который получает выходные данные из подзапроса и который также получает выходные данные из вложенного подзапроса, указанного выше. 

SELECT job_id,AVG(salary) 
FROM employees   
GROUP BY job_id   
HAVING AVG(salary)<   
(.....output from the subquery(         
output from the nested subquery)......)

Внешний запрос внутренне работает следующим образом:

Код SQL: 

SELECT job_id,AVG(salary) 
FROM employees   
GROUP BY job_id   
HAVING AVG(salary)<10000;

Внешний запрос возвращает job_id, среднюю зарплату сотрудников, которая меньше максимальной средней min_salary, возвращенной предыдущим запросом.

Выход: 

 JOB_ID AVG (SALARY)
---------- -----------
IT_PROG 5760
AC_ACCOUNT 8300
ST_MAN 7280
AD_ASST 4400
SH_CLERK 3215
FI_ACCOUNT 7920
PU_CLERK 2780
SA_REP 8350
MK_REP 6000
ST_CLERK 2785
HR_REP 6500

Пример -2: вложенные подзапросы

Вот еще один вложенный пример подзапроса.

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


Образец таблицы: клиент


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


Код SQL: 

SELECT ord_num,ord_date,ord_amount,advance_amount
FROM orders 
WHERE ord_amount>2000 
AND ord_date<'01-SEP-08' 
AND ADVANCE_AMOUNT <         
ANY(SELECT OUTSTANDING_AMT		
FROM CUSTOMER	
WHERE GRADE=3 
AND CUST_COUNTRY<>'India'	
AND opening_amt<7000 
AND EXISTS		        
(SELECT * 				 
FROM agents				  
WHERE commission<.12));

Выход: 

 ORD_NUM ORD_DATE ORD_AMOUNT ADVANCE_AMOUNT
---------- --------- ---------- --------------
    200130 30-июл-08 2500 400
    200127 20-июл-08 2500 400
    200110 15-АПР-08 3000 500
    200105 18-июл-08 2500 500
    200129 20-июл-08 2500 500
    200108 15-ФЕВ-08 4000 600
    200113 10-ИЮНЬ-08 4000 600
    200106 20-АПР-08 2500 700
    200109 30-ИЮЛ-08 3500 800
    200107 30 августа 2008 года 4500 900
    200101 15-ИЮЛ-08 3000 1000
    200128 20-ИЮЛЬ-08 3500 1500
    200114 15 августа 2008 года 3500 2000

Объяснение:

Последний внутренний запрос извлекает строки из таблицы операторов, комиссионные которых меньше 0,12%.

Второй последний внутренний запрос возвращает непогашенную сумму тем клиентам, которые относятся к 3-му классу и не принадлежат к стране Индия, а их начальная сумма открытия составляет менее 7000, и их агенты должны были получить комиссионное вознаграждение менее 0,12%.

Внешний запрос возвращает ord_num, ord_date, ord_amount, advance_amount для тех заказов из таблицы заказов, у которых ord_amount больше 2000 и ord_date до '01 -sep-08 ', и сумма аванса может быть непогашенной суммой для тех клиентов, которые находятся в классе 3 и не принадлежит к стране Индия, и там депонированная сумма открытия составляет менее 7000, и их агенты должны были заработать комиссию менее чем .12%.

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

Код SQL: 

SELECT *
FROM agents
WHERE commission<.12;

Выход: 

 AGENT_CODE AGENT_NAME WORKING_AREA COMMISSION PHONE_NO COUNTRY
---------- --------------- ----------------- -------- - --------------- ---------
A009 Бенджамин Хэмпшир .11 008-22536178
А002 Мукеш Мумбаи .11 029-12358964

Вот второй код внутреннего запроса (включая первый) с выводом:

Код SQL: 

SELECT OUTSTANDING_AMT
FROM CUSTOMER
WHERE GRADE=3 
AND CUST_COUNTRY<>'India' 
AND opening_amt<7000 
AND EXISTS(
SELECT * 
FROM agents
WHERE commission<.12);

Выход: 

 OUTSTANDING_AMT
---------------
           6000
           3000
           5000

Смотрите нашу базу данных моделей

Упражнения по SQL

Хотите улучшить вышеуказанную статью? Вносите свои заметки / комментарии / примеры через Disqus.

Предыдущая: коррелированные подзапросы с использованием псевдонимов
Далее: Союз

Новый контент: Composer: менеджер зависимостей для PHP , R программирования

script1adsense4code
script1adsense5code
disqus2code
script1adsense6code
script1adsense7code
script1adsense8code
buysellads2code