История развития пистолетов-пулеметов: Предпосылкой для возникновения пистолетов-пулеметов послужила давняя тенденция тяготения винтовок...
Своеобразие русской архитектуры: Основной материал – дерево – быстрота постройки, но недолговечность и необходимость деления...
Топ:
История развития методов оптимизации: теорема Куна-Таккера, метод Лагранжа, роль выпуклости в оптимизации...
Определение места расположения распределительного центра: Фирма реализует продукцию на рынках сбыта и имеет постоянных поставщиков в разных регионах. Увеличение объема продаж...
Основы обеспечения единства измерений: Обеспечение единства измерений - деятельность метрологических служб, направленная на достижение...
Интересное:
Влияние предпринимательской среды на эффективное функционирование предприятия: Предпринимательская среда – это совокупность внешних и внутренних факторов, оказывающих влияние на функционирование фирмы...
Что нужно делать при лейкемии: Прежде всего, необходимо выяснить, не страдаете ли вы каким-либо душевным недугом...
Подходы к решению темы фильма: Существует три основных типа исторического фильма, имеющих между собой много общего...
Дисциплины:
2020-05-08 | 197 |
5.00
из
|
Заказать работу |
SELECT EmployeeID,BonusDate,BonusAmount,3 BonusTypeID,NULL BonusPercent
FROM
(
VALUES
(1001,'20140930',300),
(1002,'20140331',500),
(1002,'20140630',500),
(1002,'20140930',500),
(1002,'20141230',500),
(1002,'20150331',500),
(1004,'20140831',200)
) indiv(EmployeeID,BonusDate,BonusAmount)
В случае необходимости, данную конструкцию можно заменить, аналогичным запросом, написанным через UNION ALL:
SELECT 1001 EmployeeID,'20140930' BonusDate,300 BonusAmount,3 BonusTypeID,NULL BonusPercent
UNION ALL
SELECT 1002,'20140331',500,3,NULL
UNION ALL
SELECT 1002,'20140630',500,3,NULL
UNION ALL
SELECT 1002,'20140930',500,3,NULL
UNION ALL
SELECT 1002,'20141230',500,3,NULL
UNION ALL
SELECT 1002,'20150331',500,3,NULL
UNION ALL
SELECT 1004,'20140831',200,3,NULL
Думаю, комментарии излишни и вам не составит большого труда разобраться с этим самостоятельно.
Так что, идем дальше.
INSERT + CTE-выражения
Совместно с INSERT можно применять CTE выражения. Для примера перепишем тот же запрос перенеся все подзапросы в блок WITH.
Для начала полностью очистим таблицу EmployeesBonus при помощи операции TRUNCATE TABLE:
TRUNCATE TABLE EmployeesBonus
Теперь перепишем запрос вынеся запросы в блок WITH:
WITH cteBonusType1 AS(
-- расчет ежемесячных бонусов
SELECT hist.EmployeeID,bdate.BonusDate,hist.Salary/100*emp.BonusPercent BonusAmount,1 BonusTypeID,emp.BonusPercent
FROM EmployeesSalaryHistory hist
JOIN
(
VALUES -- весь период работы компании - последние дни месяцев
('20131130'),
('20131231'),
('20140131'),
('20140228'),
('20140331'),
('20140430'),
('20140531'),
('20140630'),
('20140731'),
('20140831'),
('20140930'),
('20141031'),
('20141130'),
('20141230'),
('20150131'),
('20150228'),
('20150331')
) bdate(BonusDate)
ON bdate.BonusDate BETWEEN hist.DateFrom AND ISNULL(hist.DateTo,'20991231')
JOIN Employees emp ON hist.EmployeeID=emp.ID
WHERE emp.BonusPercent IS NOT NULL AND emp.BonusPercent>0
AND NOT EXISTS(-- исключаем сотрудников, которым по какой-то причине не дали бонус в указанный период
SELECT *
FROM
(
VALUES
(1001,'20140115'),
(1001,'20140430'),
(1001,'20141031'),
(1001,'20141130'),
(1001,'20150228')
) exclude(EmployeeID,BonusDate)
WHERE exclude.EmployeeID=emp.ID
AND exclude.BonusDate=bdate.BonusDate
)
),
cteBonusType2 AS(
-- годовой бонус за 2014 год - всем кто проработал больше полугода
SELECT
hist.EmployeeID,
'20141231' BonusDate,
hist.Salary/100*
CASE DepartmentID
WHEN 2 THEN 10 -- 10% от ЗП выдать Бухгалтерам
WHEN 3 THEN 15 -- 15% от ЗП выдать ИТ-шникам
ELSE 5 -- всем остальным по 5%
END BonusAmount,
2 BonusTypeID,
CASE DepartmentID
WHEN 2 THEN 10 -- 10% от ЗП выдать Бухгалтерам
WHEN 3 THEN 15 -- 15% от ЗП выдать ИТ-шникам
ELSE 5 -- всем остальным по 5%
END BonusPercent
FROM EmployeesSalaryHistory hist
JOIN Employees emp ON hist.EmployeeID=emp.ID
WHERE CAST('20141231' AS date) BETWEEN hist.DateFrom AND ISNULL(hist.DateTo,'20991231')
AND emp.HireDate<='20140601'
),
cteBonusType3 AS(
-- индивидуальные бонусы
SELECT EmployeeID,BonusDate,BonusAmount,3 BonusTypeID,NULL BonusPercent
FROM
(
VALUES
(1001,'20140930',300),
(1002,'20140331',500),
(1002,'20140630',500),
(1002,'20140930',500),
(1002,'20141230',500),
(1002,'20150331',500),
(1004,'20140831',200)
) indiv(EmployeeID,BonusDate,BonusAmount)
)
INSERT EmployeesBonus(EmployeeID,BonusDate,BonusAmount,BonusTypeID,BonusPercent)
SELECT *
FROM cteBonusType1
UNION ALL
SELECT *
FROM cteBonusType2
UNION ALL
SELECT *
FROM cteBonusType3
Как видим вынос больших подзапросов в блок WITH упростил основной запрос – сделал его более понятным.
UPDATE – обновление данных
Данный оператор в MS SQL имеет 2 формы:
1. UPDATE таблица SET … WHERE условие_выборки – обновлении строк таблицы, для которых выполняется условие_выборки. Если предложение WHERE не указано, то будут обновлены все строки. Это можно сказать классическая форма оператора UPDATE.
2. UPDATE псевдоним SET … FROM … – обновление данных таблицы участвующей в предложении FROM, которая задана указанным псевдонимом. Конечно, здесь можно и не использовать псевдонимов, используя вместо них имена таблиц, но с псевдонимом на мой взгляд удобнее.
Давайте при помощи первой формы приведем даты приема каждого сотрудника в порядок. Выполним 6 отдельных операций UPDATE:
-- приведем даты приема в порядок
UPDATE Employees SET HireDate='20131101' WHERE ID=1000
UPDATE Employees SET HireDate='20131101' WHERE ID=1001
UPDATE Employees SET HireDate='20140101' WHERE ID=1002
UPDATE Employees SET HireDate='20140601' WHERE ID=1003
UPDATE Employees SET HireDate='20140701' WHERE ID=1004
-- а здесь еще почистим поле FirstName
UPDATE Employees SET HireDate='20150101',FirstName=NULL WHERE ID=1005
Вторую форму, где применялся псевдоним, мы уже тоже успели использовать в первой части, когда обновляли поля PositionID и DepartmentID, на значения возвращаемые подзапросами:
UPDATE e
SET
PositionID=(SELECT ID FROM Positions WHERE Name=e.Position),
DepartmentID=(SELECT ID FROM Departments WHERE Name=e.Department)
FROM Employees e
Сейчас конечно данный и следующий запрос не сработают, т.к. поля Position и Department мы удалили из таблицы Employees. Вот так можно было бы представить этот запрос при помощи операций соединений:
UPDATE e
SET
PositionID=p.ID,
DepartmentID=d.ID
FROM Employees e
LEFT JOIN Positions p ON p.Name=e.Position
LEFT JOIN Departments d ON d.Name=e.Department
Надеюсь суть обновления здесь понятна, тут обновляться будут строки таблицы Employees.
Сначала вы можете сделать выборку, чтобы посмотреть какие данные будут обновлены и на какие значения:
SELECT
e.ID,
e.PositionID,e.DepartmentID, -- старые значения
e.Position,e.Department,
p.ID,d.ID, -- новые значения
p.Name,d.Name
FROM Employees e
LEFT JOIN Positions p ON p.Name=e.Position
LEFT JOIN Departments d ON d.Name=e.Department
А потом переписать это в UPDATE:
UPDATE e
SET
PositionID=p.ID,
DepartmentID=d.ID
FROM Employees e
LEFT JOIN Positions p ON p.Name=e.Position
LEFT JOIN Departments d ON d.Name=e.Department
Биохимия спиртового брожения: Основу технологии получения пива составляет спиртовое брожение, - при котором сахар превращается...
Состав сооружений: решетки и песколовки: Решетки – это первое устройство в схеме очистных сооружений. Они представляют...
Семя – орган полового размножения и расселения растений: наружи у семян имеется плотный покров – кожура...
Своеобразие русской архитектуры: Основной материал – дерево – быстрота постройки, но недолговечность и необходимость деления...
© cyberpedia.su 2017-2024 - Не является автором материалов. Исключительное право сохранено за автором текста.
Если вы не хотите, чтобы данный материал был у нас на сайте, перейдите по ссылке: Нарушение авторских прав. Мы поможем в написании вашей работы!