Справка по SQL - W Cat

1232

350

1232

400

1232

600

1233

600

1233

950

1233

980

1260

350

2111

Нет в наличии

2112

Нет в наличии

Оператор CASE может быть использован в одной из двух синтаксических форм записи:

1-я форма

CASE

WHEN

THEN

…

WHEN

THEN

[ELSE ]

END

2-я форма

CASE

WHEN

THEN

…

WHEN

THEN

[ELSE ]

END

Все предложения WHEN должны иметь одинаковую синтаксическую форму, т.е. нельзя смешивать первую и вторую формы. При использовании первой синтаксической формы условие WHEN удовлетворяется, как только значение проверяемого выражения станет равным значению выражения, указанного в предложении WHEN. При использовании второй синтаксической формы условие WHEN удовлетворяется, как только предикат принимает значение TRUE. При удовлетворении условия оператор CASE возвращает значение, указанное в соответствующем предложении THEN. Если ни одно из условий WHEN не выполнилось, то будет использовано значение, указанное в предложении ELSE. При отсутствии ELSE, будет возвращено NULL-значение. Если удовлетворены несколько условий, то будет возвращено значение предложения THEN первого из них.

В приведенном выше примере была использована вторая форма оператора CASE.

Заметим, что для проверки на NULL стандарт предлагает более короткую форму оператора - COALESCE. Этот оператор имеет произвольное число параметров и возвращает значение первого, отличного от NULL. Для двух параметров оператор COALESCE(A, B) эквивалентен следующему оператору CASE:

CASE WHEN A IS NOT NULL THEN A ELSE B END

Решение рассмотренного выше примера при использовании оператора COALESCE можно переписать следующим образом:

SELECT DISTINCT product.model,

COALESCE(CAST(price as CHAR(20)),'Нет в наличии') price

FROM product LEFT JOIN pc c ON product.model=c.model

WHERE product.type='pc';

Использование первой синтаксической формы оператора CASE можно продемонстрировать на следующем примере: Вывести все имеющиеся модели ПК с указанием цены. Отметить самые дорогие и самые дешевые модели.

SELECT DISTINCT model, price,

CASE price WHEN (SELECT MAX(price) FROM pc) THEN 'Самый дорогой'

WHEN (SELECT MIN(price) FROM pc) THEN 'Самый дешевый'

ELSE 'Средняя цена' END comment

FROM pc ORDER BY price;

В результате выполнения запроса получим

model

price

comment

1232

350

Самый дешевый

1260

350

Самый дешевый

1232

400

Средняя цена

1233

400

Средняя цена

1233

600

Средняя цена

1121

850

Средняя цена

1233

950

Средняя цена

1233

980

Самый дорогой

Функции Transact-SQL для обработки даты/времени

Стандарт SQL-92 специфицирует только функции, возвращающие системную дату/время. Например, функция CURRENT_TIMESTAMP возвращает сразу и дату, и время. Плюс имеются функции возвращающие что-либо одно.

Естественно, в силу такой ограниченности, реализации языка расширяют стандарт за счет добавления функций, облегчающий работу пользователей с данными этого типа. Здесь мы рассмотрим функции обработки даты/времени в T-SQL.

Функция DATEADD

Синтаксис

DATEADD ( datepart , number, date )

Эта функция возвращает значение типа datetime, которое получается добавлением к дате date количества интервалов типа datepart, равного number. Например, мы можем к заданной дате добавить любое число лет, дней, часов, минут и т.д. Допустимые значения аргумента datepart приведены ниже и взяты из BOL.

Datepart

Допустимые сокращения

Year - год

yy, yyyy

Quarter - квартал

qq, q

Month - месяц

mm, m

Dayofyear - день года

dy, y

Day - день

dd, d

Week - неделя

wk, ww

Hour - час

hh

Minute - минута

mi, n

Second - секунда

ss, s

Millisecond - миллисекунда

ms

Пусть сегодня 23/01/2004, и мы хотим узнать, какой день будет через неделю. Мы можем написать

SELECT DATEADD(day, 7, current_timestamp)

а можем и так

SELECT DATEADD(ww, 1, current_timestamp)

В результате получим одно и то же; что-то типа 2004-01-30 19:40:58.923.

Однако мы не можем в этом случае написать

SELECT DATEADD(mm, 1/4, current_timestamp)

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

Кроме того, мы можем использовать вместо CURRENT_TIMESTAMP функцию T-SQL GETDATE() с тем же самым эффектом. Наличие двух идентичных функций поддерживается, видимо, в ожидании последующего развития стандарта.

Пример (схема 4). Определить, какой будет день через неделю после последнего полета.

SELECT DATEADD(day, 7, (SELECT MAX(date) max_date FROM pass_in_trip))

Использование подзапроса в качестве аргумента допустимо, т.к. этот подзапрос возвращает ЕДИНСТВЕННОЕ значение типа datetime.

Функция DATEDIFF

Синтаксис

DATEDIFF ( datepart , startdate , enddate )

Функция возвращает интервал времени, прошедшего между двумя временными отметками - startdate (начальная отметка) и enddate (конечная отметка). Этот интервал может быть измерен в разных единицах. Возможные варианты определяются аргументом datepart и перечислены выше применительно к функции DATEADD.

Пример (схема 4). Определить количество дней, прошедших между первым и последним совершенными рейсами.

SELECT DATEDIFF(dd, (SELECT MIN(date) FROM pass_in_trip), (SELECT MAX(date) FROM pass_in_trip))

Пример (схема 4). Определить продолжительность рейса 1123 в минутах.

Здесь следует принять во внимание, что время вылета (time_out) и время прилета (time_in) хранится в полях типа datetime таблицы Trip. Заметим, что SQL Server вплоть до версии 2000 не имеет отдельных темпоральных типов данных для даты и времени, появление которых ожидается в следующей версии (Yukon). Поэтому при вставке в поле datetime только времени (например, UPDATE trip SET time_out = '17:24:00' WHERE trip_no=1123), время будет дополнено значением даты по умолчанию ('1900-01-01').

Напрашивающееся решение

SELECT DATEDIFF(mi, time_out, time_in) dur FROM trip WHERE trip_no=1123,

(которое дает -760) будет неверным по двум причинам.

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

Но как определить, что самолет приземлился на следующий день? Тут помогает описание предметной области, где говорится, что полет не может продолжаться более суток. Итак, если время прилета не больше, чем время вылета, то этот факт имеет место. Теперь второй вопрос: как посчитать только время, с каким бы днем оно ни стояло?

Здесь может помочь функция T-SQL DATEPART.

Функция DATEPART

Синтаксис

DATEPART ( datepart , date )

Эта функция возвращает целое число, представляющее собой указанную аргументом datepart часть заданной вторым аргументом даты (date).

Список допустимых значений аргумента datepart, описанный выше в данном разделе, дополняется еще одним значением

Datepart

Допустимые сокращения

Weekday - день недели

dw

Заметим, что возвращаемое функцией DATEPART значение в этом случае (номер дня недели) зависит от настроек, которые можно изменить с помощью оператора SET DATEFIRST, устанавливающего первый день недели. Для кого-то понедельник - день тяжелый, а для кого-то - воскресенье. Кстати, последнее значение принимается по умолчанию.

Однако вернемся к нашему примеру. В предположении, что время вылета/прилета является кратным минуте, мы можем его определить как сумму часов и минут. Поскольку функции даты/времени работают с целочисленными значениями, приведем результат к наименьшему интервалу - минутам. Итак, время вылета рейса 1123 в минутах