 |
Связанные (коррелированные) подзапросы ссылаются на атрибуты из таблицы, используемой в выходном запросе.
|
|
|
|
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
|
|
|
b) Связанные пружинные маятники.
I. Действия учителя, связанные с анализом теоретического материала темы
Активы, обязательства и капитал как элементы, связанные с измерением финансового положения
Анализ пок-лей производственной д-ти:цели, задачи, источники используемой инф-ии.
Атрибуты бренда FedEx
Атрибуты задания цветовой схемы(цвета фона, текста и гиперссылок)
Атрибуты здоровья и исцеления
Атрибуты индивидуальности бренда
Атрибуты элементов и источников сигналов.
В. ФАКТОРЫ, СВЯЗАННЫЕ СО ШКОЛОЙ
