История развития хранилищ для нефти: Первые склады нефти появились в XVII веке. Они представляли собой землянные ямы-амбара глубиной 4…5 м...
Двойное оплодотворение у цветковых растений: Оплодотворение - это процесс слияния мужской и женской половых клеток с образованием зиготы...
Топ:
Проблема типологии научных революций: Глобальные научные революции и типы научной рациональности...
Организация стока поверхностных вод: Наибольшее количество влаги на земном шаре испаряется с поверхности морей и океанов...
Выпускная квалификационная работа: Основная часть ВКР, как правило, состоит из двух-трех глав, каждая из которых, в свою очередь...
Интересное:
Мероприятия для защиты от морозного пучения грунтов: Инженерная защита от морозного (криогенного) пучения грунтов необходима для легких малоэтажных зданий и других сооружений...
Средства для ингаляционного наркоза: Наркоз наступает в результате вдыхания (ингаляции) средств, которое осуществляют или с помощью маски...
Влияние предпринимательской среды на эффективное функционирование предприятия: Предпринимательская среда – это совокупность внешних и внутренних факторов, оказывающих влияние на функционирование фирмы...
Дисциплины:
2020-05-08 | 219 |
5.00
из
|
Заказать работу |
|
|
В MS SQL для последнего примера:
SELECT
ID,
Name,
-- подзапрос 1 - получаем ID сотрудника
(SELECT TOP 1 ID FROM Employees emp WHERE emp.DepartmentID=dep.ID ORDER BY ID DESC) LastEmpID,
-- подзапрос 2 - получаем имя сотрудника
(SELECT TOP 1 Name FROM Employees emp WHERE emp.DepartmentID=dep.ID ORDER BY ID DESC) LastEmpName
FROM Departments dep
можно применить конструкцию APPLY, которая имеет 2 формы – CROSS APPLY и OUTER APPLY.
Конструкция APPLY позволяет избавиться от множества подзапросов, как в данном примере, когда требуется получить и ID и Name последнего принятого сотрудника для каждого отдела:
SELECT
ID,
Name,
empInfo.LastEmpID,
empInfo.LastEmpName
FROM Departments dep
CROSS APPLY
(
SELECT TOP 1 ID LastEmpID,Name LastEmpName
FROM Employees emp
WHERE emp.DepartmentID=dep.ID
ORDER BY emp.ID DESC
) empInfo
ID | Name | LastEmpID | LastEmpName |
1 | Администрация | 1000 | Иванов И.И. |
2 | Бухгалтерия | 1002 | Сидоров С.С. |
3 | ИТ | 1004 | Николаев Н.Н. |
Здесь подзапрос блока CROSS APPLY выполнится для каждого значения строки из таблицы Departments. Если подзапрос строки не вернет, то данный отдел исключается из результирующего списка.
Если требуется, чтобы были возвращены все строки таблицы Departments, то используйте следующую форму этого оператора OUTER APPLY:
SELECT
ID,
Name,
empInfo.LastEmpID,
empInfo.LastEmpName
FROM Departments dep
OUTER APPLY
(
SELECT TOP 1 ID LastEmpID,Name LastEmpName
FROM Employees emp
WHERE emp.DepartmentID=dep.ID
ORDER BY emp.ID DESC
) empInfo
ID | Name | LastEmpID | LastEmpName |
1 | Администрация | 1000 | Иванов И.И. |
2 | Бухгалтерия | 1002 | Сидоров С.С. |
3 | ИТ | 1004 | Николаев Н.Н. |
4 | Маркетинг и реклама | NULL | NULL |
5 | Логистика | NULL | NULL |
В общем, достаточно полезный оператор, который в некоторых ситуациях сильно упрощающий запрос. Данный подзапрос так же сработает для каждой строки результирующего набора, т.е. для каждого переданного параметра, но сработает он намного эффективнее, чем в случае использования множества подзапросов. С прочими деталями в случае применения APPLY, например, для случая, когда подзапрос возвращает несколько строк, я думаю вы сможете разобраться самостоятельно. Ладно, раз уж заговорил об этом, то приведу небольшой пример для самостоятельного разбора:
|
SELECT dep.ID,dep.Name,pos.PositionID,pos.PositionName
FROM Departments dep
CROSS APPLY
(
SELECT ID PositionID,Name PositionName
FROM Positions
) pos
Использование подзапросов в блоке WHERE
Для примера получим отделы, в которых числится более двух сотрудников:
SELECT *
FROM Departments dep
WHERE (SELECT COUNT(*) FROM Employees emp WHERE emp.DepartmentID=dep.ID)>2
Так как здесь мы используем оператор сравнения, то соответственно подзапрос должен возвращать максимум одну строку и одно значение, т.е. так же когда подзапрос используется и в блоке SELECT.
Конструкции EXISTS и NOT EXISTS
Позволяют проверить есть ли соответствующие условию записи в подзапросе:
-- отделы в которых есть хотя бы один сотрудник
SELECT *
FROM Departments dep
WHERE EXISTS(SELECT * FROM Employees emp WHERE emp.DepartmentID=dep.ID)
-- отделы в которых нет ни одного сотрудника
SELECT *
FROM Departments dep
WHERE NOT EXISTS(SELECT * FROM Employees emp WHERE emp.DepartmentID=dep.ID)
Здесь все просто – EXISTS возвращает True, если подзапрос возвращает хотя бы одну строку, и False, если подзапрос не возвращает строк. NOT EXISTS – инверсия результата.
Конструкция IN и NOT IN с подзапросом
До этого мы рассматривали IN с перечислением значений. Так же можно использовать его с подзапросом, который возвращает перечень этих значений:
-- отделы где есть сотрудники
SELECT *
FROM Departments
WHERE ID IN(SELECT DISTINCT DepartmentID FROM Employees WHERE DepartmentID IS NOT NULL)
-- отделы где нет сотрудников
SELECT *
FROM Departments
WHERE ID NOT IN(SELECT DISTINCT DepartmentID FROM Employees WHERE DepartmentID IS NOT NULL)
Обратите внимание, что я исключил NULL значение используя условие (DepartmentID IS NOT NULL) в подзапросе. NULL значения в данном случае так же опасны – смотрите об этом в описании конструкции IN во второй части.
|
|
История развития пистолетов-пулеметов: Предпосылкой для возникновения пистолетов-пулеметов послужила давняя тенденция тяготения винтовок...
Двойное оплодотворение у цветковых растений: Оплодотворение - это процесс слияния мужской и женской половых клеток с образованием зиготы...
Археология об основании Рима: Новые раскопки проясняют и такой острый дискуссионный вопрос, как дата самого возникновения Рима...
Своеобразие русской архитектуры: Основной материал – дерево – быстрота постройки, но недолговечность и необходимость деления...
© cyberpedia.su 2017-2024 - Не является автором материалов. Исключительное право сохранено за автором текста.
Если вы не хотите, чтобы данный материал был у нас на сайте, перейдите по ссылке: Нарушение авторских прав. Мы поможем в написании вашей работы!