Максим Чалышев - Oracle SQL. 100 шагов от новичка до профессионала

В некоторых задачах необходимо применить фильтр уже для результата группировки записей. Подобный фильтр применяется, например, при ограничении количества выводимых групп.

Перед нами неизбежно возникает необходимость ограничить результаты вывода групп.

Например, у нас есть группировка автомобилей по марке.

В нашей таблице есть несколько марок машин; теперь попробуйте представить ситуацию, когда в этой таблице были бы машины всех возможных марок.

Для того чтобы установить критерии отбора результатов группировки, в языке SQL есть специальный оператор HAVING, используемый совместно с GROUP BY.

Синтаксис

Здесь после HAVING условие — это условие ограничения на вывод групп.

Примеры

Сгруппировать данные в таблице AUTO по цвету (COLOR), вывести только группы красных авто и синих.

Рисунок 106. Группировка по цвету: таблица AUTO

Сгруппировать данные в таблице AUTO по марке, найти количество в группе, выбрать только группы, где больше одного авто.

Рисунок 107. Группировка по первой букве

Сгруппировать данные по первой букве имени (FIRSTNAME), найти все группы, где средний возраст (YEAROLD) людей (MAN) больше 29 лет.

Рисунок 108. Использование HAVING: запрос

В ORACLE SQL существует специальный идентификатор для каждой строки таблицы — это колонка ROWID, уникальный идентификатор строки.

Что такое ROWID? Уникальный идентификатор: состоит из номера объекта (32 бита), относительного номера файла (10 бит), номера блока (22 бита) и номера строки (16 бит).

Как использовать ROWID? Если нет другого уникального идентификатора, первичного ключа, то можно опираться на ROWID.

Пример работы с ROWID — находим дубли.

Пример работы с ROWID — находим количество повторенийю

Пример работы с ROWID — сортировка.

Для удаления дубликатов строк в таблице без первичного ключа можно обратиться именно к ROWID-колонке.

Пример удаления дублей из таблицы AUTO

В этом примере группируем таблицу AUTO по всем колонкам, находим MAX (ROWID) сгруппированных записей и удаляем те записи, ROWID которых нет в сгруппированном наборе данных.

• Оператор HAVING нельзя использовать в запросах без GROUP BY.

• Оператор HAVING возможно также применять вместе c сортировкой ORDER BY.

Синтаксис

Примеры

Сгруппировать данные в таблице AUTO по цвету, найти количество авто в каждой группе, отсортировать по цвету.

Сгруппировать данные в таблице AUTO по цвету, найти количество авто в каждой группе.

Отсортировать количество авто в каждой группе.

Есть ли другие способы удаления дублей из таблиц?

Да, например с использованием EXIST, с использованием аналитических функций.

Используем EXIST:

Будет ли работать HAVING в запросах с несколькими таблицами?

Да, ограничений нет.

Пример запроса:

Ранее мы уже встречались с оператором IN в запросах SQL в инструкции WHERE, в качестве фильтра по перечислению значений.

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

Важным свойством использования IN является то, что в качестве источника значений для списка оператора может являться другой подзапрос.

Вспомните синтаксис запроса с инструкцией IN, который мы проходили ранее.

SELECT * или колонки через запятую FROM таблиц WHERE имяколонки IN (перечень значений).

В рассматриваемом нами варианте в этом шаге вместо списка значений мы сможем задать подзапрос, возвращающий необходимые нам значения.

Синтаксис

Примеры

Вы помните, по каким колонкам связаны таблицы в нашей схеме? Если нет, то посмотрите, пожалуйста, на схему ниже.

AUTO — > MAN = PHONENUM

CITY — > MAN = CITYCODE

Это может понадобиться при рассмотрении примеров.

Выбрать все машины * AUTO, где владельцам (MAN) больше 35 лет (YEAROLD). Использовать IN с подзапросом.