Главная | Обратная связь | Поможем написать вашу работу!
МегаЛекции

Связанные (коррелированные) подзапросы ссылаются на атрибуты из таблицы, используемой в выходном запросе.




SELECT custid, orderid, orderdate, empid

FROM Sales.Orders AS O1

WHERE orderid =

(SELECT MAX(O2.orderid)

FROM Sales.Orders AS O2

WHERE O2.custid = O1.custid);

Внешний запрос обращен кэкземпляру таблицы orders, названному О1; он отбирает заказы, в которых ID заказа равен значению, возвращенному подзапросом.

Подзапрос выбирает из второго экземпляра таблицы Orders, названного О2, заказы, у которых внутренний ID клиента равен внешнему ID клиента, и возвращает максимальный ID заказа среди отобранных. Проще говоря, для каждой строки из О1 подзапрос должен вернуть максимальный ID заказа для текущего клиента. Если ID заказа в О1 и возвращенный подзапросом ID заказа совпадают, ID заказа из О1 является максимальным для текущего клиента и в этом случае строка экземпляра О1 возвращается запросом.

Результат:

 

Рассмотрим, например, строку результата

85 10739 2007-11-12 00:00:00.000 3

С учетом данной выходной строки, когда вычисляется подзапрос, связь или ссылка на О1.custid равна 85. После подстановки значения 85 вы получите следующий запрос:

SELECT MAX(О2.orderid)

FROM Sales.Orders AS О2

WHERE О2.custid = 85;

 

Пример:

SELECT emp_lname FROM employee

WHERE 'p3' IN (SELECT project_no

FROM works_on

WHERE works_on.emp_no = employee.emp_no);

 

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

Как система может обрабатывать запрос в примере? Во-первых, система отыскивает первую строку в таблице employee (для внешнего запроса) и сравнивает номер служащего в этом столбце со значениями столбца works_on.emp_no во внутреннем запросе. Если условие внешнего запроса (WHERE 'p3' IN) не выполнено, то ни одна строка не возвращается внешнему запросу для этого служащего.

Затем система отыскивает следующую строку в таблице employee и повторяет сравнение номеров служащих в обеих таблицах.

Если один из элементов результирующего набора равен значению рЗ, то условие возвращает истинное значение, и отображается соответствующее значение столбца emp_lname.

 

Предикат EXISTS

Как было сказано выше, предикат EXISTS принимает на входе подзапрос, а на выходе возвращает значение TRUE, если подзапрос отбирает хотя бы одну строку, и FALSE в противном случае.

 

Пример: запрос возвращает клиентов из Испании, сделавших заказы:

SELECT custid, companyname

FROM Sales.Customers AS C

WHERE country = N'Spain'

AND EXISTS

(SELECT * FROM Sales.Orders AS O

WHERE O.custid = C.custid);

Внешний запрос к таблице Customers отбирает только клиентов из Испании, для которых предикат EXISTS возвращает значение TRUE.

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

Пример: выбрать служащих, которые работают над проектом p1.

SELECT emp_lname

FROM employee

WHERE EXISTS

(SELECT * FROM works_on WHERE employee.emp_no = works_on.emp_no AND project_no = 'p1');

 

В предикате EXISTS можно задавать отрицание.

Пример: запрос возвращает клиентов из Испании, не сделавших ни одного заказа.

SELECT custid, companyname

FROM Sales.Customers AS C

WHERE country = N'Spain'

AND NOT EXISTS

(SELECT * FROM Sales.Orders AS O

WHERE O.custid = C.custid);

 

Пример: выбрать служащих, которые не работают в отделах. расположенных в Seattle.

SELECT emp_lname

FROM employee

WHERE NOT EXISTS

(SELECT * FROM department WHERE employee.dept_no = department.dept_no AND location = 'Seattle');

Пример: база данных сдачи сессии в некотором учебном заведении из трех отношений R1 = (ФИО, Дисциплина, Оценка); R2 = (ФИО, Группа); R3 = (Группы, Дисциплина)

R1
ФИО Дисциплина Оценка
Петров Ф. И. Базы данных  
Сидоров К. А. Базы данных  
……………. ………………….. ….
Миронов А. В. Теория информации Null
……………. ………………….. ….
     
R2
ФИО Группа
Петров Ф. И.  
Сидоров К. А.  
…………… ……….
       

 

R3
Группа Дисциплина
  Базы данных
  Теория информации
……. …………………..

 

Список тех, кто сдал все положенные экзамены:

SELECT ФИО

FROM R1 as a

WHERE Оценка > 2

GROUP BY ФИО

HAVING COUNT(*) = (SELECT COUNT(*)

FROM R2, R3

WHERE R2.Группа=R3.Группа AND ФИО= a.ФИО)

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

 

Список тех, кто должен был сдавать экзамен по БД, но пока еще не сдавал.

SELECT ФИО

FROM R2, R3

WHERE R2.Группа=R3.Группа AND Дисциплина = "БД" AND NOT EXISTS (SELECT ФИО

FROM R1

WHERE ФИО= R2.ФИО AND Дисциплина = "БД")

Пример: в коррелированном вложенном запросе используется ключевое слово IN. Это запрос, зависящий от результатов выполнения другого запроса. Запрос повторно выполняется для каждой строки, выбранной с помощью другого запроса. Данный запрос получает имена и фамилии сотрудников, для которых значение премии в таблице SalesPerson составляет 5000.00, а соответствующие им идентификационные номера в таблицах Employee и SalesPerson совпадают.

USE AdventureWorks;

GO

SELECT DISTINCT c.LastName, c.FirstName

FROM Person.Contact AS c JOIN HumanResources.Employee AS e

ON e.ContactID = c.ContactID WHERE 5000.00 IN

(SELECT Bonus

FROM Sales.SalesPerson sp

WHERE e.EmployeeID = sp.SalesPersonID);

GO

Примечание: вложенный запрос данной инструкции не может быть выполнен независимо от внешнего запроса. Требуется значение параметра Employee.EmployeeID, однако в процессе обработки строк Employee компонентом SQL Server 2005 Database Engine указанное значение меняется.

 

Пример: Коррелированный вложенный запрос также может использоваться в предложении HAVING внешнего запроса. В данном примере осуществляется поиск моделей продуктов, для которых максимальная цена в каталоге в два раза превышает среднюю цену по нему.

USE AdventureWorks

GO

SELECT p1.ProductModelID

FROM Production.Product AS p1

GROUP BY p1.ProductModelID

HAVING MAX(p1.ListPrice) >= ALL

(SELECT 2 * AVG(p2.ListPrice)

FROM Production.Product AS p2

WHERE p1.ProductModelID = p2.ProductModelID);

GO

 

 

Поделиться:





Читайте также:





Воспользуйтесь поиском по сайту:



©2015 - 2024 megalektsii.ru Все авторские права принадлежат авторам лекционных материалов. Обратная связь с нами...