Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 6: упорядочивание выборки
2.1.6. Пример 6: упорядочивание выборки
Задача 2.1.6.a{35}: показать все книги в библиотеке в порядке возрастания их года издания.
Задача 2.1.6.b{36}: показать все книги в библиотеке в порядке убывания их года издания.
Ожидаемый результат 2.1.6.a.
b_name |
b_year |
Курс теоретической физики |
1981 |
Евгений Онегин |
1985 |
Сказка о рыбаке и рыбке |
1990 |
Искусство программирования |
1993 |
Язык программирования С++ |
1996 |
Психология программирования |
1998 |
Основание и империя |
2000 |
Ожидаемый результат 2.1.6.b. |
|
b_name |
b_year |
Основание и империя |
2000 |
Психология программирования |
1998 |
Язык программирования С++ |
1996 |
Искусство программирования |
1993 |
Сказка о рыбаке и рыбке |
1990 |
Евгений Онегин |
1985 |
Курс теоретической физики |
1981 |
Решение 2.1.6.a{35}.
Для упорядочивания1 результатов выборки необходимо применить конструкцию ORDER BY (строка 4 каждого запроса), в которой мы указываем:
•поле, по которому производится сортировка (b_year)
•направление сортировки (ASC).
MySQL Решение 2.1.6.a
1SELECT `b_name`,
2`b_year`
3 |
|
FROM |
`books` |
|
|
|
|
4 |
|
ORDER |
BY `b_year` ASC |
MS SQL Решение 2.1.6.a
1SELECT [b_name],
2[b_year]
3 |
|
FROM |
[books] |
4 |
|
ORDER |
BY [b_year] ASC |
|
|
|
|
1 В повседневной жизни всё равно большинство людей использует тут термин «сортировка» вместо «упорядочивание», но это не совсем идентичные понятия. «Сортировка» больше относится к процессу изменения порядка, а «упорядочивание» к готовому результату.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 35/545
Пример 6: упорядочивание выборки
Oracle Решение 2.1.6.a
1SELECT "b_name",
2"b_year"
3 |
|
FROM |
"books" |
4 |
|
ORDER |
BY "b_year" ASC |
Решение 2.1.6.b{35}.
Здесь (в отличие от решения{35} задачи 2.1.6.a{35}) всего лишь необходимо поменять направление сортировки с «по возрастанию» (ASC) на «по убыванию»
(DESC).
MySQL Решение 2.1.6.b
1SELECT `b_name`,
2`b_year`
3 |
|
FROM |
`books` |
4 |
|
ORDER |
BY `b_year` DESC |
|
|
|
|
MS SQL Решение 2.1.6.b
1SELECT [b_name],
2[b_year]
3 |
|
FROM |
[books] |
4 |
|
ORDER |
BY [b_year] DESC |
Oracle Решение 2.1.6.b
1SELECT "b_name",
2"b_year"
3 |
|
FROM |
"books" |
4 |
|
ORDER |
BY "b_year" DESC |
Альтернативное решение можно получить добавлением знака «минус» перед именем поля, по которому сортировка реализована по возрастанию, т.е. ORDER
BY числовое_поле DESC эквивалентно ORDER BY -числовое_поле ASC.
MySQL Решение 2.1.6.b (альтернативный вариант)
1SELECT `b_name`,
2`b_year`
3 |
|
FROM |
`books` |
4 |
|
ORDER |
BY -`b_year` ASC |
|
|
|
|
MS SQL Решение 2.1.6.b (альтернативный вариант)
1SELECT [b_name],
2[b_year]
3 |
|
FROM |
[books] |
4 |
|
ORDER |
BY -[b_year] ASC |
Oracle Решение 2.1.6.b (альтернативный вариант)
1SELECT "b_name",
2"b_year"
3 |
|
FROM |
"books" |
4 |
|
ORDER |
BY -"b_year" ASC |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 36/545
Пример 6: упорядочивание выборки
Исследование 2.1.6.EXP.A. Ещё одна неожиданная проблема с упорядочиванием связана с тем, где различные СУБД по умолчанию располагают NULL-значения — в начале выборки или в конце. Проверим.
Снова воспользуемся готовой таблицей table_with_nulls{26}. В ней попрежнему находятся следующие данные:
x
1
1
2
NULL
Выполним запросы 2.1.6.EXP.A:
MySQL |
Исследование 2.1.6.EXP.A |
||
1 |
SELECT |
`x` |
|
2 |
FROM |
`table_with_nulls` |
|
3 |
ORDER |
BY `x` DESC |
|
|
|
||
MS SQL |
Исследование 2.1.6.EXP.A |
||
1 |
SELECT |
[x] |
|
2 |
FROM |
[table_with_nulls] |
|
3 |
ORDER |
BY [x] DESC |
|
|
|
|
|
Oracle |
|
Исследование 2.1.6.EXP.A |
|
1 |
SELECT |
"x" |
|
2 |
FROM |
"table_with_nulls" |
|
3 |
ORDER |
BY "x" DESC |
|
Результаты будут следующими (обратите внимание на то, где расположено NULL-значение):
x |
|
x |
|
x |
2 |
|
2 |
|
NULL |
1 |
|
1 |
|
2 |
1 |
|
1 |
|
1 |
NULL |
|
NULL |
|
1 |
MySQL |
MS SQL Server |
Oracle |
||
Получить в MySQL и MS SQL Server поведение, аналогичное поведению Oracle (и наоборот), можно с использованием следующих запросов:
MySQL |
Исследование 2.1.6.EXP.A |
||
1 |
SELECT |
`x` |
|
2 |
FROM |
`table_with_nulls` |
|
3 |
ORDER |
BY `x` IS NULL DESC, |
|
4 |
|
|
`x` DESC |
|
|
||
MS SQL |
Исследование 2.1.6.EXP.A |
||
1 |
SELECT |
[x] |
|
2 |
FROM |
[table_with_nulls] |
|
3 |
ORDER |
BY ( CASE |
|
4 |
|
|
WHEN [x] IS NULL THEN 0 |
5 |
|
|
ELSE 1 |
6 |
|
|
END ) ASC, |
7 |
|
|
[x] DESC |
|
|
|
|
Oracle |
|
Исследование 2.1.6.EXP.A |
|
1 |
SELECT |
"x" |
|
2 |
FROM |
"table_with_nulls" |
|
3 |
ORDER |
BY "x" DESC NULLS LAST |
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 37/545
Пример 6: упорядочивание выборки
В случае с Oracle мы явно указываем, поместить ли NULL-значения в начало выборки (NULLS FIRST) или в конец выборки (NULLS LAST). Поскольку MySQL и MS SQL Server не поддерживают такой синтаксис, выборку приходится упорядочивать по двум уровням: первый уровень (строка 3 для MySQL, строки 3-6 для MS SQL Server) — по признаку «является ли значение поля NULL’ом», второй уровень
— по самому значению поля.
Задание 2.1.6.TSK.A: показать список авторов в обратном алфавитном порядке (т.е. «Я А».
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 38/545
Пример 7: использование составных условий
2.1.7. Пример 7: использование составных условий
Задача 2.1.7.a{39}: показать книги, изданные в период 1990-2000 годов, представленные в библиотеке в количестве трёх и более экземпляров.
Задача 2.1.7.b{40}: показать идентификаторы и даты выдачи книг за лето 2012-го года.
Ожидаемый результат 2.1.7.a.
|
b_name |
b_year |
b_quantity |
|
Сказка о рыбаке и рыбке |
1990 |
3 |
||
Основание и империя |
2000 |
5 |
||
Язык программирования С++ |
1996 |
3 |
||
Искусство программирования |
1993 |
7 |
||
|
Ожидаемый результат 2.1.7.b. |
|
||
|
|
|
|
|
sb_id |
sb_start |
|
|
|
42 |
2012-06-11 |
|
|
|
57 |
2012-06-11 |
|
|
|
Решение 2.1.7.a{39}.
Для каждой СУБД приведено два варианта запроса — с ключевым словом BETWEEN (часто используемого как раз для указания диапазона дат), и без него — в виде двойного неравенства (что выглядит более привычно для имеющих опыт программирования).
В случае использования BETWEEN, границы включаются в диапазон искомых значений.
В представленных ниже решениях отдельные условия осознанно не взяты в скобки. Синтаксически такой вариант верен и отлично работает, но он тем сложнее читается, чем больше составных частей входит в сложное условие. Потому всё же рекомендуется брать каждую отдельную часть в скобки.
MySQL Решение 2.1.7.a
1-- Вариант 1: использование BETWEEN
2SELECT `b_name`,
3`b_year`,
4`b_quantity`
5 |
|
FROM |
`books` |
|
6 |
|
WHERE |
`b_year` BETWEEN 1990 |
AND 2000 |
7 |
|
|
AND `b_quantity` >= 3 |
|
|
|
|
|
|
1-- Вариант 2: использование двойного неравенства
2SELECT `b_name`,
3`b_year`,
4`b_quantity`
5 |
|
FROM |
`books` |
6 |
|
WHERE |
`b_year` >= 1990 |
7AND `b_year` <= 2000
8AND `b_quantity` >= 3
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 39/545