INSERT #Trash(ID,Col1,Col2,Col3)VALUES
(1,'A','A','A'), (2,'A','B','C'), (3,'C','A','B'), (4,'A','A','B'),
(5,'B','B','B'), (6,'A','A','B'), (7,'A','A','A'), (8,'C','A','B'),
(9,'C','A','B'), (10,'A','A','B'), (11,'A',NULL,'B'), (12,'A',NULL,'B')
-- посмотрим что возвращает запрос без опции DISTINCT
SELECT Col1,Col2,Col3
FROM #Trash
-- посмотрим что возвращает запрос с опцией DISTINCT
SELECT DISTINCT Col1,Col2,Col3
FROM #Trash
-- удалим временную таблицу
DROP TABLE #Trash
Наглядно это будет выглядеть следующим образом (все дубликаты помечены одним цветом):
Теперь давайте рассмотрим где это можно применить, на более практичном примере – вернем из таблицы Employees только уникальные идентификаторы отделов (т.е. узнаем ID отделов в которых числятся сотрудники):
SELECT DISTINCT DepartmentID
FROM Employees
| DepartmentID |
Здесь мы получили три строки, т.к. 2 сотрудника у нас числятся в одном отделе (ИТ).
Теперь узнаем в каких отделах, какие должности фигурируют:
SELECT DISTINCT DepartmentID,PositionID
FROM Employees
| DepartmentID | PositionID |
Здесь мы получили 4 строчки, т.к. повторяющихся комбинаций (DepartmentID, PositionID) в нашей таблице нет.
Ненадолго вернемся к DDL
Так как данных для демонстрационных примеров начинает не хватать, а рассказать хочется более обширно и понятно, то давайте чуть расширим нашу таблицу Employess. К тому же немного вспомним DDL, как говорится «повторение – мать учения», и плюс снова немного забежим вперед и применим оператор UPDATE:
-- создаем новые колонки
ALTER TABLE Employees ADD
LastName nvarchar(30), -- фамилия
FirstName nvarchar(30), -- имя
MiddleName nvarchar(30), -- отчество
Salary float, -- и конечно же ЗП в каких-то УЕ
BonusPercent float -- процент для вычисления бонуса от оклада
GO
-- наполняем их данными (некоторые данные намерено пропущены)
UPDATE Employees
SET
LastName=N'Иванов',FirstName=N'Иван',MiddleName=N'Иванович',
Salary=5000,BonusPercent= 50
WHERE ID=1000 -- Иванов И.И.
UPDATE Employees
SET
LastName=N'Петров',FirstName=N'Петр',MiddleName=N'Петрович',
Salary=1500,BonusPercent= 15
WHERE ID=1001 -- Петров П.П.
UPDATE Employees
SET
LastName=N'Сидоров',FirstName=N'Сидор',MiddleName=NULL,
Salary=2500,BonusPercent=NULL
WHERE ID=1002 -- Сидоров С.С.
UPDATE Employees
SET
LastName=N'Андреев',FirstName=N'Андрей',MiddleName=NULL,
Salary=2000,BonusPercent= 30
WHERE ID=1003 -- Андреев А.А.
Убедимся, что данные обновились успешно:
SELECT *
FROM Employees
| ID | Name | … | LastName | FirstName | MiddleName | Salary | BonusPercent |
| Иванов И.И. | Иванов | Иван | Иванович | ||||
| Петров П.П. | Петров | Петр | Петрович | ||||
| Сидоров С.С. | Сидоров | Сидор | NULL | NULL | |||
| Андреев А.А. | Андреев | Андрей | NULL |
Задание псевдонимов для столбцов запроса
Думаю, здесь будет проще показать, чем написать:
SELECT
-- даем имя вычисляемому столбцу
LastName+' '+FirstName+' '+MiddleName AS ФИО,
-- использование двойных кавычек, т.к. используется пробел
HireDate AS "Дата приема",
-- использование квадратных скобок, т.к. используется пробел
Birthday AS [Дата рождения],
-- слово AS не обязательно
Salary ZP
FROM Employees
| ФИО | Дата приема | Дата рождения | ZP |
| Иванов Иван Иванович | 2015-04-08 | 1955-02-19 | |
| Петров Петр Петрович | 2015-04-08 | 1983-12-03 | |
| NULL | 2015-04-08 | 1976-06-07 | |
| NULL | 2015-04-08 | 1982-04-17 |
Как видим заданные нами псевдонимы столбцов, отразились в заголовке результирующей таблицы. Собственно, это и есть основное предназначение псевдонимов столбцов.
Обратите внимание, т.к. у последних 2-х сотрудников не указано отчество (NULL значение), то результат выражения «LastName+' '+FirstName+' '+MiddleName» так же вернул нам NULL.
Для соединения (сложения, конкатенации) строк в MS SQL используется символ «+».
Запомним, что все выражения в которых участвует NULL (например, деление на NULL, сложение с NULL) будут возвращать NULL.
На заметку.
В случае ORACLE для объединения строк используется оператор «||» и конкатенация будет выглядеть как «LastName||' '||FirstName||' '||MiddleName». Для ORACLE стоит отметить, что у него для строковых типов есть исключение, для них NULL и пустая строка '' это одно и тоже, поэтому в ORACLE такое выражение вернет для последних 2-х сотрудников «Сидоров Сидор » и «Андреев Андрей ». На момент версии ORACLE 12c, насколько я знаю, опции которая изменяет такое поведение нет (если не прав, прошу поправить меня). Здесь мне сложно судить хорошо это или плохо, т.к. в одних случаях удобнее поведение NULL-строки как в MS SQL, а в других как в ORACLE.
В ORACLE тоже допустимы все перечисленные выше псевдонимы столбцов, кроме […].
Для того чтобы не городить конструкцию с использованием функции ISNULL, в MS SQL мы можем применить функцию CONCAT. Рассмотрим и сравним 3 варианта:
SELECT
LastName+' '+FirstName+' '+MiddleName FullName1,
-- 2 варианта для замены NULL пустыми строками '' (получаем поведение как и в ORACLE)
ISNULL(LastName,'')+' '+ISNULL(FirstName,'')+' '+ISNULL(MiddleName,'') FullName2,
CONCAT(LastName,' ',FirstName,' ',MiddleName) FullName3
FROM Employees
| FullName1 | FullName2 | FullName3 |
| Иванов Иван Иванович | Иванов Иван Иванович | Иванов Иван Иванович |
| Петров Петр Петрович | Петров Петр Петрович | Петров Петр Петрович |
| NULL | Сидоров Сидор | Сидоров Сидор |
| NULL | Андреев Андрей | Андреев Андрей |
В MS SQL псевдонимы еще можно задавать при помощи знака равенства:
SELECT
'Дата приема'=HireDate, -- помимо "…" и […] можно использовать '…'
[Дата рождения]=Birthday,
ZP=Salary
FROM Employees
Использовать для задания псевдонима ключевое слово AS или же знак равенства, наверное, больше дело вкуса. Но при разборе чужих запросов, данные знания могут пригодиться.
Напоследок скажу, что для псевдонимов имена лучше задавать, используя только символы латиницы и цифры, избегая применения '…', "…" и […], то есть использовать те же правила, что мы использовали при наименовании таблиц. Дальше, в примерах я буду использовать только такие наименования и никаких '…', "…" и […].
Основные арифметические операторы SQL
| Оператор | Действие |
| + | Сложение (x+y) или унарный плюс (+x) |
| - | Вычитание (x-y) или унарный минус (-x) |
| * | Умножение (x*y) |
| / | Деление (x/y) |
| % | Остаток от деления (x%y). Для примера 15%10 даст 5 |
Приоритет выполнения арифметических операторов такой же, как и в математике. Если необходимо, то порядок применения операторов можно изменить используя круглые скобки — (a+b)*(x/(y-z)).
И еще раз повторюсь, что любая операция с NULL дает NULL, например: 10+NULL, NULL*15/3, 100/NULL – все это даст в результате NULL. Т.е. говоря просто неопределенное значение не может дать определенный результат. Учитывайте это при составлении запроса и при необходимости делайте обработку NULL значений функциями ISNULL, COALESCE:
SELECT
ID,Name,
Salary/100*BonusPercent AS Result1, -- без обработки NULL значений
Salary/100*ISNULL(BonusPercent,0) AS Result2, -- используем функцию ISNULL
Salary/100*COALESCE(BonusPercent,0) AS Result3 -- используем функцию COALESCE
FROM Employees
| ID | Name | Result1 | Result2 | Result3 |
| Иванов И.И. | ||||
| Петров П.П. | ||||
| Сидоров С.С. | NULL | |||
| Андреев А.А. | ||||
| Николаев Н.Н. | NULL | |||
| Александров А.А. | NULL |
Немного расскажу о функции COALESCE:
COALESCE (expr1, expr2, ..., exprn) - Возвращает первое не NULL значение из списка значений.
Пример:
SELECT COALESCE(f1, f1*f2, f2*f3) val -- в данном случае вернется третье значение
FROM (SELECT null f1, 2 f2, 3 f3) q
В основном, я сосредоточусь на рассказе конструкций языка DML и по большей части не буду рассказывать о функциях, которые будут встречаться в примерах. Если вам непонятно, что делает та или иная функция поищите ее описание в интернет, можете даже поискать информацию сразу по группе функций, например, задав в поиске Google «MS SQL строковые функции», «MS SQL математические функции» или же «MS SQL функции обработки NULL». Информации по функциям очень много, и вы ее сможете без труда найти. Для примера, в библиотеке MSDN, можно узнать больше о функции COALESCE:
Вырезка из MSDN Сравнение COALESCE и CASE
Выражение COALESCE — синтаксический ярлык для выражения CASE. Это означает, что код COALESCE(expression1,...n) переписывается оптимизатором запросов как следующее выражение CASE:
CASE
WHEN (expression1 IS NOT NULL) THEN expression1
WHEN (expression2 IS NOT NULL) THEN expression2
...
ELSE expressionN
END
Для примера рассмотрим, как можно воспользоваться остатком от деления (%). Данный оператор очень полезен, когда требуется разбить записи на группы. Например, вытащим всех сотрудников, у которых четные табельные номера (ID), т.е. те ID, которые делятся на 2:
SELECT ID,Name
FROM Employees
WHERE ID%2=0 -- остаток от деления на 2 равен 0
| ID | Name |
| Иванов И.И. | |
| Николаев Н.Н. | |
| Сидоров С.С. |
ORDER BY – сортировка результата запроса
Предложение ORDER BY используется для сортировки результата запроса.
SELECT
LastName,
FirstName,
Salary
FROM Employees
ORDER BY LastName,FirstName -- упорядочить результат по 2-м столбцам – по Фамилии, и после по Имени
| LastName | FirstName | Salary |
| Андреев | Андрей | |
| Иванов | Иван | |
| Петров | Петр | |
| Сидоров | Сидор |
После имя поля в предложении ORDER BY можно задать опцию DESC, которая служит для сортировки этого поля в порядке убывания:
SELECT LastName,FirstName,Salary
FROM Employees
ORDER BY -- упорядочить в порядке
Salary DESC, -- 1. убывания Заработной Платы
LastName, -- 2. по Фамилии
FirstName -- 3. по Имени
| LastName | FirstName | Salary |
| Иванов | Иван | |
| Сидоров | Сидор | |
| Андреев | Андрей | |
| Петров | Петр |
Для заметки. Для сортировки по возрастанию есть ключевое слово ASC, но так как сортировка по возрастанию применяется по умолчанию, то про эту опцию можно забыть (я не помню случая, чтобы я когда-то использовал эту опцию).
Стоит отметить, что в предложении ORDER BY можно использовать и поля, которые не перечислены в предложении SELECT (кроме случая, когда используется DISTINCT, об этом случае я расскажу ниже). Для примера забегу немного вперед используя опцию TOP и покажу, как например, можно отобрать 3-х сотрудников у которых самая высокая ЗП, с учетом что саму ЗП в целях конфиденциальности я показывать не должен:
SELECT TOP 3 -- вернуть только 3 первые записи из всего результата
ID,LastName,FirstName
FROM Employees
ORDER BY Salary DESC -- сортируем результат по убыванию Заработной Платы
| ID | LastName | FirstName |
| Иванов | Иван | |
| Сидоров | Сидор |
Конечно здесь есть случай, что у нескольких сотрудников может быть одинаковая ЗП и тут сложно сказать каких именно трех сотрудников вернет данный запрос, это уже нужно решать с постановщиком задачи. Допустим, после обсуждения с постановщиком данной задачи, вы согласовали и решили использовать следующий вариант – сделать дополнительную сортировку по полю даты рождения (т.е. молодым у нас дорога), а если и дата рождения у нескольких сотрудников может совпасть (ведь такое тоже не исключено), то можно сделать третью сортировку по убыванию значений ID (в последнюю очередь под выборку попадут те, у кого ID окажется максимальным – например, те кто был принят последним, допустим табельные номера у нас выдаются последовательно):
SELECT TOP 3 -- вернуть только 3 первые записи из всего результата
ID,LastName,FirstName
FROM Employees
ORDER BY
Salary DESC, -- 1. сортируем результат по убыванию Заработной Платы
Birthday, -- 2. потом по Дате рождения
ID DESC -- 3. и для полной однозначности результата добавляем сортировку по ID
Т.е. вы должны стараться чтобы результат запроса был предсказуемым, чтобы вы могли в случае разбора полетов объяснить почему в «черный список» попали именно эти люди, т.е. все было выбрано честно, по утверждённым правилам.
Сортировать можно так же используя разные выражения в предложении ORDER BY:
SELECT LastName,FirstName
FROM Employees
ORDER BY CONCAT(LastName,' ',FirstName) -- используем выражение
Так же в ORDER BY можно использовать псевдонимы заданные для колонок:
SELECT CONCAT(LastName,' ',FirstName) fi
FROM Employees
ORDER BY fi -- используем псевдоним
Стоит отметить что в случае использования предложения DISTINCT, в предложении ORDER BY могут использоваться только колонки, перечисленные в блоке SELECT. Т.е. после применения операции DISTINCT мы получаем новый набор данных, с новым набором колонок. По этой причине, следующий пример не отработает:
SELECT DISTINCT
LastName,FirstName,Salary
FROM Employees
ORDER BY ID -- ID отсутствует в итоговом наборе, который мы получили при помощи DISTINCT
Т.е. предложение ORDER BY применяется уже к итоговому набору, перед выдачей результата пользователю.
Примечание 1. Так же в предложении ORDER BY можно использовать номера столбцов, перечисленных в SELECT:
SELECT LastName,FirstName,Salary
FROM Employees
ORDER BY -- упорядочить в порядке
3 DESC, -- 1. убывания Заработной Платы
1, -- 2. по Фамилии
2 -- 3. по Имени
Для начинающих выглядит удобно и заманчиво, но лучше забыть и никогда не использовать такой вариант сортировки.
Если в данном случае (когда поля явно перечислены), такой вариант еще допустим, то для случая с использованием «*» такой вариант лучше никогда не применять. Почему – потому что, если кто-то, например, поменяет в таблице порядок столбцов, или удалит столбцы (и это нормальная ситуация), ваш запрос может так же работать, но уже неправильно, т.к. сортировка уже может идти по другим столбцам, и это коварно тем что данная ошибка может обнаружиться очень нескоро.
В случае, если бы столбы были явно перечислены, то в вышеуказанной ситуации, запрос либо бы продолжал работать, но также правильно (т.к. все явно определено), либо бы он просто выдал ошибку, что данного столбца не существует.
Так что можете смело забыть, о сортировке по номерам столбцов.
Примечание 2.
В MS SQL при сортировке по возрастанию NULL значения будут отображаться первыми.
SELECT BonusPercent FROM Employees ORDER BY BonusPercent
Соответственно при использовании DESC они будут в конце