Полезные запросы на языке SQL в БД Oracle 10g/11g

Снова всем привет!

Сегодня бы я хотел поделиться с Вами полезными запросами на Sql в БД Oracle.
В силу своей специальности, я каждый день работаю с этой базой данных, приходится писать очень много запросов, иногда некоторые из них такие большие и сложные, что аж сам порой ужасаюсь!Но сегодня я поделюсь с Вами маленькими версиями запросов, но очень полезными и нужными, которые практически каждый день мне приходится использовать.
Итак, начнем!

1. Бывают такие случаи, когда в таблице имеются поля, к примеру адреса, т.е. одно поле улица, другое поле — это номер дома и хочется эти поля объединить в одно поле (помню такое мне необходимо было, когда строил базу данных для Google maps).
Допустим поле улица называется «Street», а поле дом — «House», поле Street содержит строчку «ул.Урицкого», а поле House — «54», тогда пишем следующий код:

SELECT t.street || ', ' || t.house AS full_street
FROM your_table t

В результате у Вас, помимо ваших полей в таблице, добавится еще одно поле «full_street», которое будет содержать, такую строчку «ул. Урицкого, 54».

Поясню — эти «вертикальные палочки» нечто иное как оператор конкатенации, который работает только со строковыми полями, поэтому перед этим нужно убедиться, что Ваши поля имеют строковый тип. Поле «ful_street», который мы определили — это синоним, т.е. как будет называться ваше конечное поле в таблице, для этого мы и поставили «as».

Вот и все, очень простенький запрос, но в некоторых случаях, очень полезный.

2. Следующий наш запрос будет еще полезнее, т.к. с этим запросом точно приходится каждый день работать и не по одному разу, а именно — это поиск дублей в таблице. Эх, дубли, вечная проблема и головная боль:) Я думаю, что многие сталкивались с этим! Ну да ладно, перейдем к делу.

Допустим, у Вас есть таблица, где одно поле это «id», второе — «ФИО», вот будет такая маленькая таблица, но у Вас может быть сколько угодной полей, просто желательно, чтобы был айдишник (id). Код будет такой:

SELECT t.id, COUNT(*)
FROM your_table t
GROUP BY t.id
HAVING COUNT (*) >1

В результате данного запроса будет поле с айдишником, и поле с количеством (т.е. сколько раз повторяется данный айдишник). Здесь мы применили простую группировку. Having как раз и фильтрует группы. Если Вы мало знакомы с группировкой, здесь лучше почитать книгу по Sql, т.к. тема группировки достаточно большая и выходит за рамки статьи. Просто знайте, что данный запрос будет работать всегда:). Если запрос ничего не показал — значит дублей нет, если показал какие-то записи, значит, дубли!

3. Следующий запрос достаточно маленький, но не очень легкий в качестве понимания, зато очень полезный, поэтому разберем его подробнее.
Допустим, у Вас есть таблица с полями: id, класс машины, поле с названием самой машины и поле с ценой. Причем, в классе может быть много всяких названий машин.Например,

id класс машины название машины цена
1 легкий Хонда 50
2 легкий Мазда 100
3 легкий Лексус 200
4 тяжелый Камаз 200

И Вам хочется найти 2 наиболее максимальных по цене машин в каждом классе. Результат должен быть 3 автомобиля: Мазда, Лексус и Камаз.

Когда я учил в университете БД Microsoft SQL Server, то эту проблему можно было решить оператором «top», но в Oracle это делается немного по другому, я бы сказал немного сложнее, но функциональней. Перейдем к написанию самого запроса:

SELECT *
FROM
(SELECT t.id,
t.klas_avto,
t.name_avto,
t.cena_avto,
rank() OVER (partition BY t.klas_avto ORDER BY t.cena_avto DESC) AS rank_avto
FROM test t) tt
WHERE tt.rank_avto IN (1,2)

В этом запросе мы использовали под запрос (если Вы не знакомы с ними, то можно почитать в какой-нибудь книжке, т.к. это выходит за рамки статьи).

Оконная функция rank выдаст Вам результат с двумя авто в классе «легкий» и их максимальной ценой и авто «Камаз» в классе «тяжелый». Вы спросите, а почему в классе «тяжелый» только одно авто, если в запросе стоит условия на выбор двух? А потому, что в классе «тяжелый» только одно авто, запрос это и выдаст. Разберем подробнее сам запрос:

  • Идет простой селект;
  • Затем функция rank (rank() over (partition by — это такой синаксис), т.к. нам нужно найти максимальные цены машин в классе, то и группируем по классу (поле partition by t.klas_avto), затем сортируем по возрастанию нашу цену в классе (order by t.cena_avto desc);
  • Завершающий момент — в условии where выбираем 2 максимальных цены. Если Вам нужно три максимальных, то допишите «3» в условие, т.ею будет выглядеть так «where tt.rank_avto in (1,2,3)

Вот такой хитренький запрос:)
На сегодня это все приемы написания полезных запросов на SQL.
Надеюсь они будут полезны Вам.
Если что-то останется непонятным для Вас, пишите в комментариях, разберем вместе еще раз:).

Успехов!

3 мыслей про “Полезные запросы на языке SQL в БД Oracle 10g/11g

    • Да, извиняюсь. Скрипт, который делает подствеку кода, неправильно символ перевел. Исправлю.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

*

Этот сайт использует Akismet для борьбы со спамом. Узнайте как обрабатываются ваши данные комментариев.