W Cat - Справка по SQL
SELECT DATEPART(hh, time_out)*60 + DATEPART(mi, time_out) FROM trip WHERE trip_no=1123
и время прилета
SELECT DATEPART(hh, time_in)*60 + DATEPART(mi, time_in) FROM trip WHERE trip_no=1123
Теперь мы должны сравнить, превышает ли время прилета время вылета. Если это так, вычесть из первого второе, чтобы получить продолжительность рейса. В противном случае к разности нужно добавить одни сутки (24*60 = 1440 минут).
SELECT CASE WHEN time_dep=time_arr THEN time_arr-time_dep+1440 ELSE time_arr-time_dep END dur FROM
( SELECT DATEPART(hh, time_out)*60 + DATEPART(mi, time_out) time_dep, DATEPART(hh, time_in)*60 + DATEPART(mi, time_in) time_arr FROM trip WHERE trip_no=1123) tm
Здесь, чтобы не повторять длинные конструкции в операторе CASE, использован подзапрос. Конечно, результат получился достаточно громоздким, зато абсолютно корректным в свете сделанных к этой задаче замечаний.
Пример (4 схема). Определить дату и время вылета рейса 1123.
В таблице совершенных рейсов Pass_in_trip содержится только дата рейса, но не время, т.к. в соответствии с предметной областью каждый рейс может выполняться только один раз в день. Для решения этой задачи нужно к дате, хранящейся в таблице Pass_in_trip, добавить время из таблицы Trip
SELECT pt.trip_no, DATEADD(mi, DATEPART(hh,time_out)*60 + DATEPART(mi,time_out), date) [time]
FROM pass_in_trip pt JOIN trip t ON pt.trip_no=t.trip_no WHERE t.trip_no=1123
Выполнив запрос, получим следующий результат
Trip_no
Time
1123
2003-04-05 16:20:00.000
1123
2003-04-08 16:20:00.000
DISTINCT необходим здесь, чтобы исключить возможные дубликаты, поскольку номер и дата рейса дублируются в этой таблице для каждого пассажира данного рейса.
Функция DATENAME
Синтаксис
DATENAME ( datepart , date )
Эта функция возвращает символьное представление составляющей (datepart ) указанной даты (date). Аргумент, определяющий составляющую даты, может принимать одно из значений, перечисленных в вышеприведенной таблице.
Это дает нам простую возможность конкатенировать компоненты даты, получая любой нужный формат представления. Например, конструкция
SELECT DATENAME ( weekday , '2003-12-31' )+', '+DATENAME ( day , '2003-12-31' )+' '+ DATENAME ( month , '2003-12-31' )+' '+DATENAME ( year , '2003-12-31' )
даст нам следующий результат
Wednesday, 31 December 2003
Следует отметить, что данная функция выявляет отличие значений day и dayofyear аргумента datepart. Первый дает символьное представление дня указанной даты, в то время как второй дает символьное представление этого дня от начала года. Т.е.
SELECT DATENAME ( day , '2003-12-31' )
даст нам 31, а
SELECT DATENAME ( dayofyear , '2003-12-31' )
- 365.
В ряде случаев функцию DATEPART можно заменить более простыми функциями. Вот они:
DAY ( date ) - целочисленное представление дня указанной даты. Эта функция эквивалентна функции DATEPART(dd, date).
MONTH ( date ) - целочисленное представление месяца указанной даты. Эта функция эквивалентна функции DATEPART(mm, date).
YEAR ( date ) - целочисленное представление года указанной даты. Эта функция эквивалентна функции DATEPART(yy, date).
Функции работы со строками в MS SQL SERVER 2000
Вот полный перечень функций работы со строками, взятый из BOL:
ASCII
NCHAR
SOUNDEX
CHAR
PATINDEX
SPACE
CHARINDEX
REPLACE
STR
DIFFERENCE
QUOTENAME
STUFF
LEFT
REPLICATE
SUBSTRING
LEN
REVERSE
UNICODE
LOWER
RIGHT
UPPER
LTRIM
RTRIM
Начнем с двух взаимно обратных функций - ASCII и CHAR.
Функция ASCII возвращает ASCII-код крайнего левого символа строкового выражения, являющегося аргументом функции.
Вот, например, как можно определить, сколько имеется разных букв, с которых начинаются названия кораблей в таблице Ships:
SELECT COUNT(DISTINCT ASCII(name)) FROM Ships
Результат - 11. Чтобы выяснить, какие это буквы, мы можем применить функцию CHAR, которая возвращает символ по известному ASCII-коду (от 0 до 255):
SELECT DISTINCT CHAR(ASCII(name)) FROM Ships ORDER BY 1
Следует отметить, что аналогичный результат можно получить проще с помощью еще одной функции - LEFT, которая имеет следующий синтаксис:
и вырезает заданное вторым аргументом число символов слева из строки, являющейся первым аргументом. Итак,
SELECT DISTINCT LEFT(name, 1) FROM Ships ORDER BY 1
А вот как, например, можно получить таблицу кодов всех алфавитных символов:
SELECT CHAR(ASCII('a')+ num-1) letter, ASCII('a')+ num - 1 [code]
FROM (SELECT 5*5*(a-1)+5*(b-1) + c AS num
FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) x
CROSS JOIN
(SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) y
CROSS JOIN
(SELECT 1 c UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) z
) x
WHERE ASCII('a')+ num -1 BETWEEN ASCII('a') AND ASCII('z')
Тех, кто еще не в курсе генерации числовой последовательности, отсылаю к соответствующей статье .
Как известно, коды строчных и прописных букв отличаются. Поэтому чтобы получить полный набор без переписывания запроса, достаточно просто дописать к вышеприведенному коду аналогичный:
UNION
SELECT CHAR(ASCII('A')+ num-1) letter, ASCII('A')+ num - 1 [code]
FROM (SELECT 5*5*(a-1)+5*(b-1) + c AS num
FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) x
CROSS JOIN
(SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) y
CROSS JOIN
(SELECT 1 c UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) z
) x
WHERE ASCII('A')+ num -1 BETWEEN ASCII('A') AND ASCII('Z')
Чтобы таблица выглядела более патриотично, достаточно заменить латинские буквы "a" и "A" на неотличимые на взгляд русские - "а" и "А", а "z" и "Z" на "я" и "Я". Вот только буквы "ё" вы не увидите в этой таблице, т.к. в кодовой таблице ASCII эти символы лежат отдельно, что легко проверить:
SELECT ASCII('ё') UNION ALL SELECT ASCII('Ё')
Я полагаю, что не будет сложным добавить эту букву в таблицу, если потребуется.
Рассмотрим теперь задачу определения нахождения искомой подстроки в строковом выражении. Для этого могут использоваться две функции - CHARINDEX и PATINDEX. Обе они возвращают начальную позицию (позицию первого символа подстроки) подстроки в строке. Функция CHARINDEX имеет синтаксис:
CHARINDEX (искомое_выражение, строковое_выражение[, стартовая_позиция])
Здесь необязательный целочисленный параметр стартовая_позиция определяет позицию в строковом выражении, начиная с которой выполняется поиск искомого_выражения . Если этот параметр опущен, поиск выполняется от начала строкового_выражения. Например, запрос
SELECT name FROM Ships WHERE CHARINDEX('sh', name) 0
будет выводить те корабли, в которых имеется сочетание символов "sh". Здесь используется тот факт, что если искомая строка не будет обнаружена, то функция CHARINDEX возвращает 0. Результат выполнения запроса будет содержать следующие корабли:
name
Kirishima
Musashi
Washington
Следует отметить, что если искомая подстрока либо строковое выражение есть NULL, то результатом функции тоже будет NULL.
Следующий пример определяет позиции первого и второго вхождения символа "a" в имени корабля "California"
SELECT CHARINDEX('a',name) first_a,
CHARINDEX('a', name, CHARINDEX('a', name)+1) second_a
FROM Ships WHERE name='California'
Обратите внимание, что при определении второго символа в функции используется стартовая позиция, которой является позиция следующего за первой буквой "a" символа - CHARINDEX('a', name)+1. Правильность результата - 2 и 10 - легко проверить :-).
Функция PATINDEX имеет синтаксис:
Главное отличие этой функции от CHARINDEX заключается в том, что поисковая строка может содержать подстановочные знаки - % и _. При этом концевые знаки "%" являются обязательными. Например, использование этой функции в первом примере будет иметь вид
SELECT name FROM Ships WHERE PATINDEX('%sh%', name) 0
А вот, например, как можно найти имена кораблей, которые содержат последовательность из трех символов, первый и последний из которых есть "e":
SELECT name FROM Ships WHERE PATINDEX('%e_e%', name) 0
Результат выполнения этого запроса выглядит следующим образом:
name
Revenge
Royal Sovereign
Парная к LEFT функция RIGHT возвращает заданное число символов справа из строкового выражения:
