Какие опции могут быть использованы в операторах выборки данных

Какие опции могут быть использованы в операторах выборки данных

Основным действием выполняемым с помощью SQL запросов в системе ZuluGIS является выборка данных для их вывода в виде таблицы в области результатов запроса. Выборка данных производится с помощью ключевого слова SELECT , после которого задаются параметры выборки.

Как правило, выборка данных производится из записей полей БД слоев карты, но ключевое слово SELECT также может использоваться для вывода в поле результатов вычисления произвольных выражений, приведенных в строке после SELECT . Можно одновременно вывести результаты вычисления нескольких выражений, перечислив их через запятую.

Например, команда SELECT «Результаты вычисления», 2+2 , выведет в области результатов таблицу из двух ячеек с данными « Результаты вычисления » и 4 .

Строка команды выборки состоит из трех основных частей, в которых задается какие поля таблиц выводятся в итоговую таблицу, из каких слоев берутся данные и по каким условиям отбираются данные для итоговой таблицы.

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

Типовой запрос имеет следующий вид:

SELECT [ FROM ] [ WHERE ]

SELECT

Часть команды выборки в которой задаются колонки выводимой таблицы данных. В области через запятую перечисляются названия полей, значения которых выводятся в колонках таблицы, либо выражения, результаты расчета которых выводятся в таблице («Операторы и функции языка SQL»).

Для вывода в таблице значений всех полей данных из указанных в запросе слоев, задайте вместо списка полей символ « * » . В таком случае, значения полей в таблице будут выводиться в том же порядке, в котором они заданы в БД.

Для того чтобы в итоговой таблице выводились только отличающиеся друг от друга записи, задайте списком полей ключевое слово DISTINCT («Избавление от повторяющихся записей»).

FROM

Часть строки выборки в которой через запятую перечисляются слои карты из которых запрашиваются данные.

Если все поля в запросе указаны в формате . , часть запроса с ключевым словом FROM может быть опущена.

Если данные запрашиваются из более чем одного слоя карты, в итоговой таблице будет выведено декартово пересечение записей запрошенных слоев. Например, в случае запроса полей из двух слоев в итоговой таблице будет набор записей со всеми возможными комбинациями полей из записей первого и второго слоя, т.е., например при запросе поля А из слоя содержащего 2 записи и запросе поля B из слоя также содержащего две записи, в итоговой таблице будет четыре записи со следующими данными: A1 + B1 , A1 + B2 , A2 + B1 , A2 + B2 .

WHERE

Часть, в которой задаются условия, в соответствии с которыми отбираются записи данных в таблицу результатов.

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

Если в таблице результатов требуется вывести все записи для указанных полей, эта часть запроса может быть опущена.

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

Примеры выборок

Простейшая выборка

SELECT * FROM Кварталы

В результате данного запроса выводится таблицу со всеми записями данных об объектах слоя Кварталы , причем в таблице выводятся все доступные поля данных слоя.

Рисунок 714. Пример выполнения запроса

Команда выборки с перечислением требуемых полей

SELECT Sys, perimeter, [Количество этажей] FROM Здания

В результате запроса выводится таблица с полями Sys , perimeter , Количество этажей всех записей слоя Здания .

Команда выборки без FROM части

SELECT Здания.Sys, Здания.Адрес

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

Выборка с отбором по условию

SELECT [Номер дома] FROM Здания WHERE Улица='5й Южный пер.'

В результате данного запроса выводится таблица со значениями поля Номер дома для всех записей слоя Здания у которых значение поля Улица равняется строке 5й Южный пер. .

Выборка по нескольким слоям

SELECT Кварталы.sys, Здания.Улица + " " + Здания.[Номер дома] FROM Здания, Кварталы WHERE Здания.Geometry.STWithin(Кварталы.Geometry)

В результате такого запроса будут отобраны объекты слоя Здания располагаются в объектах слоя Квартал и будет выведена таблица из двух столбцов, в первом из которых выводятся поля Sys объектов слоя Квартал , а во втором — адреса зданий в слое Здания помещающихся в указанных объектах слоя Квартал . Используемая в данном запросе конструкция Здания.Geometry.STWithin(Кварталы.Geometry) проверяет, не располагается ли объект слоя Здания внутри объекта слоя Кварталы (подробнее «Работа с пространственными данными в запросах»).

3.4 Команда выборки данных

Одной из главных функций SQL считается выполнение выборки. Поэтому рассмотрим этот процесс подробно.

Выборка – это обращение к БД с целью извлечь данные в виде, удобном для пользователя. Для выборки применяются запросы к БД. Иногда в SQL выделяют даже раздел, который называют языком запросов к данным DQL (Data Query Language). Фактически этот раздел языка ANSI SQL представлен только одной командой – SELECT. Но эта команда достаточно обширна. Она является ядром языка SQL. и используется для реализации операций проекции, ограничения, расширения.

Для пользователя РБД оператор SELECT является, пожалуй, одним из наиболее главных и полезных операторов языка SQL. Этот оператор позволяет производить:

• выбор данных (отбор записей и полей);
• вычисления и сравнения;
• упорядочение записей при выводе содержимого таблиц;
• группирование данных и применение к этим группам специальных групповых операций.

• инструкция языка SQL – это предложение (команда, оператор);
• отдельные составные части инструкции (список полей, спецификация) – это опции предложения;
• любая спецификация – это фраза, отвечающая требованиям синтаксиса предложения.

• основная часть команды SELECT …FROM обязательна;
• опция предикат необязательна
• дополнительные части WHERE,GROUPBY,ORDERBYнеобязательны, они следуют за FROM;
• опция HAVING не может применяться без GROUP BY.

• при наличии слова DISTINCT эти записи не учитываются;
• функция COUNT возвратит значение нуль;
• функция COUNT (*) обработает все записи так же, как обычные значения;
• другие функции обычно возвращают Null-значение.

• числа сравниваются алгебраически; отрицательные числа считаются меньшими, чем положительные, независимо от их абсолютной величины;
• строки символов сравниваются в соответствии с их представлением в коде, используемом в конкретной СУБД, например, в коде ASCII;
• если сравниваются две строки символов, имеющих разные длины, то перед выполнением операции сравнения их длина уравнивается до большей за счет добавления пробелов справа в короткой строке.

• символ _ (подчеркивание) заменяет любой одиночный символ;
• символ % (процент) заменяет любую последовательность из N символов, где N может быть нулем;
• все другие символы означают сами себя.

• SELECT ↔ проекция и расширение;
• FROM ↔ произведение;
• WHERE ↔ ограничение.

Выборка данных: оператор SELECT

Для формирования запросов на выборку данных в SQL используется оператор SELECT. Его формат представлен ниже:

SELECT [ ALL | DISTINCT ] select_item_cominalist FROM table_reference_commalist [ WHERE conditional_expression ]

[ GROUP BY column_name_commalist ]

[ ORDER BY order_item_commalist ]

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

• 1) выполняется раздел FROM;
• 2) выполняется раздел WHERE (если есть);
• 3) выполняется GROUP BY (если есть);
• 4) выполняется HAVING (если есть);
• 5) выполняются определения в разделе SELECT;
• 6) выполняется ORDER BY (если есть).

Начнем с рассмотрения обязательного раздела SELECT. В нем указывается список элементов выборки select-item-commalist, который не должен быть пустым. Также может использоваться ключевое слово ALL или DISTINCT. Первое из них указывает, что в результате запроса могут быть повторяющиеся строки, второе – что повторения отбрасываются. Например, используется ключевое слово DISTINCT и есть три совпадающих строки, тогда в результате из них останется только одна. Когда явно ничего не указано, то подразумевается ALL.

Вернемся к списку элементов выборки. В нем через запятую могут указываться имена столбцов, константы, операции над столбцами или функции, возвращающие скалярное значение. Кроме того, может использоваться символ «*», обозначающий все столбцы, или .*, что указывает на все столбцы данной таблицы.

Перед тем как перейти к рассмотрению примеров, необходимо отметить, что в разделе FROM указываются таблицы, из которых делается выборка. Если через запятую перечислены несколько таблиц, это соответствует их декартову произведению. Следующий запрос позволит получить содержимое всей таблицы Т1:

SELECT * FROM T1 Аналогичный результат даст запрос SELECT Tl.* FROM Т1

Пусть имеется таблица Students, аналогичная представленной в табл. 7.2. Получить перечень номеров студенческих групп без повторения позволит следующий запрос:

SELECT DISTINCT [Group] FROM Students

По поводу этого запроса надо отметить следующее. Во-первых, предполагается, что номер группы может упоминаться в таблице несколько раз. Поэтому для отбрасывания повторений явно указывается ключевое слово DISTINCT. Если бы в перечне столбцов был первичный или альтернативный ключ, это обеспечило бы уникальность строк в результате запроса и DISTINCT можно было бы опустить. Во-вторых, название столбца Group совпадает с названием инструкции SQL. Поэтому в большинстве случаев потребуется явно указать в СУБД, что речь идет о названии столбца. В частности, для MS SQL Server надо будет использовать двойные кавычки или квадратные скобки: [Group].

Рассмотрим пример с заданием имени столбца и использованием текстовой константы в столбце. Если необходимо явно указать, как столбец будет называться в выводимых результатах запроса, это можно сделать в списке элементов выборки в разделе SELECT. Новое имя указывается после исходного названия столбца через пробел или после необязательного ключевого слова «as». Ниже приведен пример, в котором список фамилий и инициалов студентов сопровождается подписью «Фамилия и инициалы»:

SELECT DISTINCT ‘Фамилия и инициалы’ as Labell, FIO FROM Students

Подпись задается с помощью строковой константы, которые в SQL берутся в одинарные кавычки. Называться столбец с подписью будет Labell. Результат выполнения этого запроса для набора данных из табл. 7.2 представлен в табл. 7.6. Как отмечалось выше, ключевое слово «as» в SELECT можно пропустить, но иногда оно позволяет сделать текст на SQL более понятным.

Результат запроса

SQL-Ex blog

Использование предложения TOP в операторе SELECT

Добавил Sergey Moiseenko on Суббота, 22 июля. 2023

Иногда вам может потребовать вернуть только несколько строк из результирующего набора, а не все строки. Это может быть полезно, если вы хотите просто проверить критерий отбора на нескольких строках данных. Какой бы ни была причина, предложение TOP может быть полезно для возвращения конкретного числа или процента строк из результирующего набора. В этой статье будет рассмотрено использование предложения TOP в операторе SELECT, и как он может использоваться для возвращения частичного набора записей.

Синтаксис предложения TOP

Синтаксис предложения TOP простой:

TOP () [PERCENT] 
[ WITH TIES ]

expression — выражение, которое определяет число возвращаемых строк. Выражение оценивается как float, если используется опция PERCENT, в противном случае — bigint.

PERCENT — когда указано, возвращается процентное отношение строк, а не их конкретное число.

WITH TIES – это необязательный параметр, но при использовании будут возвращаться две или более строк, которые имеют то же самое значение, что и последнее значение в упорядоченном ограниченном наборе. Предложение ORDER BY должно быть включено, если используется опция WITH TIES. Использование этой опции может означать, что возвращаться может больше строк, чем дает вычисленное выражение expression.

Здесь мы будем обсуждать использование предложения TOP только в операторе SELECT. Однако предложение TOP также может использоваться в операторах UPDATE, INSERT, DELETE и MERGE, и в этих случаях оно будет контролировать число модифицируемых или удаляемых строк. Обратитесь к документации Microsoft за получением полной информации о синтаксисе и использовании предложения TOP.

Данные для примеров

Тестовые данные для всех примеров этой статьи будут базироваться на таблице dbo.Inventory, которая создается в базе данных tempdb. Эта таблица создается и заполняется данными с помощью кода в листинге 1.

-- Создаем тестовые данные 
USE tempdb; 
GO 
CREATE TABLE dbo.Inventory( 
ID int IDENTITY(1,1) NOT NULL, 
ProductName varchar(50) NULL, 
Quantity int NULL, 
Price money NULL, 
PriceChangeDate datetime NULL 
) 
GO 
INSERT INTO dbo.Inventory VALUES 
('Hammer',10,12.99,SYSDATETIME()), 
('8 mm socket',5,3.99,SYSDATETIME()), 
('7 mm socket',5,3.99,SYSDATETIME()), 
('9 mm socket',5,3.99,SYSDATETIME()), 
('6 mm socket',5,3.59,SYSDATETIME()), 
('Flat head #3',15,3.99,SYSDATETIME()), 
('Flat head #1', 15,2.99,SYSDATETIME()), 
('Flat head #2',15,3.59,SYSDATETIME()); 
GO

Листинг 1. Создание и заполнение данными таблицы dbo.Inventory

Таблица dbo.Inventory содержит различные типы инструментов. В следующем разделе эта таблица будет использоваться для демонстрации работы предложения TOP в операторе SELECT. Если вы хотите выполнять примеры из этой статьи, то можете использовать код в листинге 1 для создания таблицы dbo.Inventory в базе данных tempdb на одном из ваших тестовых экземпляров SQL Server.

Простое предложение TOP

Наиболее простой и обычный способ использования предложения TOP заключается в выборке конкретного числа строк. Код в листинге 2 показывает использование простого предложения TOP для получения 5 строк из таблицы dbo.Inventory.

USE tempdb; 
GO 
SELECT TOP (5) * 
FROM dbo.Inventory; 
GO

Листинг 2. Возврат 5 несортированных строк с помощью предложения TOP

При выполнении кода в листинге 2 получен результат, представленный в Отчете 1. (Без использования предложения ORDER BY возможен вывод других 5 строк, но для такого небольшого набора данных, который мы создали, это маловероятно. Вариации в выводе обычно заметны, когда обрабатывается больше строк.)

Отчет 1. Результаты выполнения листинга 1

В отчете 1 показаны 5 строк, полученных из таблицы dbo.Inventory при выполнении листинга 2. Эти строки несортированы, поскольку предложение ORDER BY не было задано в операторе SELECT.

Для возвращения первых 5 строк из таблицы dbo.Inventory на основе сортировки по столбцу ProductName можно выполнить код в листинге 3.

USE tempdb; 
GO 
SELECT TOP (5) * 
FROM dbo.Inventory 
ORDER BY ProductName; 
GO

Листинг 3. Получение 5 строк из отсортированного набора

При выполнении кода в листинге 3 будет получен следующий результат:

Отчет 2. Результаты выполнения листинга 3

Если предложение ORDER BY включено в запрос, SQL Server сначала сортирует результирующий набор, прежде чем определить возвращаемые строки. Если посмотреть вывод в Отчете 3, можно увидеть, что SQL Server вернул 5 строк, и каждая строка отсортирована на основе столбца ProductName.

До сих пор в примерах число возвращаемых строк было основано на жестко закодированном значении. В данном случае этим числом было «5». Число возвращаемых строк не обязательно жестко кодировать, его можно получить из переменной.

Использование переменной в предложении TOP

Иногда вам может потребоваться вернуть из конкретного запроса число строк на основе переменной. При этом число возвращаемых строк может разниться при всяком выполнении запроса. Для демонстрации я буду использовать хранимую процедуру, создаваемую в листинге 4.

USE tempdb; 
GO 
CREATE PROCEDURE dbo.Get_TopX (@TopX int) 
AS 
SELECT TOP (@TopX) * 
FROM dbo.Inventory; 
GO 
EXEC dbo.Get_TopX @TopX = 2;

Листинг 4. Хранимая процедура для возвращения числа строк на основе переменной

В листинге была создана хранимая процедура dbo.Get_TopX. Эта процедура требует передачи параметра с именем @TopX при выполнении. Эта переменная используется для определения числа строк, которое будет возвращать предложение TOP при выполнении хранимой процедуры. При выполнении оператора EXEC в листинге 4 будут получены результаты, показанные в Отчете 4.

Отчет 4. Результаты, полученные при выполнении хранимой процедуры dbo.Get_TopX в листинге 4

Различное число строк может возвращаться просто передачей различных значений для входного параметра хранимой процедуры dbo.Get_TopX. Я оставлю вам протестировать это.

Пара небольших замечаний. Значение в предложении TOP не может быть отрицательным, или будет возвращаться ошибка. Если значением является 0, то будет возвращено 0 строк.

Кроме того, вы можете использовать выражение в предложении TOP. Замените в предыдущей процедуре на TOP (@TopX -1), и тогда при передаче значения параметра 2 на выходе будет одна строка.

Возвращение процентного отношения строк при использовании предложения TOP

Пока все примеры с предложением TOP возвращали заданное число строк из набора записей на основе числа, указанного в предложении TOP. Это предложением поддерживает также возвращение процентного отношения строк из результирующего набора, а не заданного числа строк. Как это делается показано в листинге 5 на примере оператора SELECT.

USE tempdb; 
GO 
SELECT TOP (50) PERCENT * 
FROM dbo.Inventory 
WHERE ProductName like '% mm %'; 
GO

Листинг 5. Возвращение процентного отношения числа строк при использовании предложения TOP

Результаты выполнения кода листинга 5 показаны в Отчете 4.

Результаты выполнения кода листинга 5

Оператор SELECT из листинга 5 вернул только 2 строки, что точно составляет 50 процентов от числа инструментов для метрических головок, находящихся в таблице dbo.Inventory.

При использовании опции PERCENT в предложении TOP вычисленное число возвращаемых строк может иметь дробное значение. При вычислении дробного числа SQL Server округляет его к следующему целому значению, чтобы получить число возвращаемых строк. Для демонстрации рассмотрим код в листинге 6.

USE tempdb; 
GO 
SELECT TOP (51) PERCENT * 
FROM dbo.Inventory 
WHERE ProductName like '%socket%'; 
GO

Листинг 6. Возвращается 51 процент строк

При выполнении кода в листинге 6 получаются следующие результаты.

Отчет 5. Результаты при выполнении листинга 6

Когда SQL Server обрабатывает код в листинге 5, он обнаруживает, что 4 строки имеют название “socket” в ProductName. 51 процент от 4 строк дает дробное значение 2.04. Поскольку SQL Server не может вернуть дробное число строк, значение округляется к следующему целому значению — вот почему было получено 3 строк при выполнении листинга 6.

Использование опции WITH TIES

Предложение TOP имеет также опцию WITH TIES. Эта опция полезна для возвращения всех строк, которые имеют то же самое значение, что и последняя строка. Под последней строкой я имею в виду номер строки, который равен числу в предложении TOP. При использовании опции WITH TIES оператор должен также включать предложение ORDER BY, в противном случае возникнет следующая ошибка:

Msg 1062, Level 15, State 1, Line 78
The TOP N WITH TIES clause is not allowed without a corresponding ORDER BY clause.

Чтобы увидеть, как вернуть связанные записи, рассмотрите два оператора SELECT в листинге 7.

USE tempdb; 
GO 
SELECT TOP (2) WITH TIES * 
FROM dbo.Inventory 
WHERE Price < 3.60
ORDER BY Price; 
GO 
SELECT TOP (2) * 
FROM dbo.Inventory 
WHERE Price < 3.60
ORDER BY Price; 
GO

Листинг 7. Использование опции WITH TIES

При выполнении кода в листинге 7 первый оператор SELECT возвращает строки, показанные в отчете 6, а второй оператор SELECT — в отчете 7.

Отчет 6. Возвращаемые строки, когда используется опция WITH TIES

Отчет 7. Возвращаемые строки, когда опция WITH TIES не используется

Оба оператора SELECT в листинге 7 используют одинаковое предложение TOP (2). Единственное отличие состоит в том, что в первом операторе SELECT используется опция WITH TIES, а во втором — нет. По результатам в отчетах 6 и 7 можно увидеть, что опция WITH TIES приводит к выводу 3 строк, а без нее возвращается только 2 строки. Опция WITH TIES возвращает третью строку в листинге 6 потому, что эта строка имеет тот же самое значение в столбце Price, что и вторая строка (или последняя строка на основе числа в предложении TOP).

Логическая обработка предложений TOP и ORDER BY

Иногда вы можете получить в чем-то неожиданные результаты, обусловленные логическим порядком обработки предложений TOP и ORDER BY. Эти неожиданные результаты могут иметь место, когда предложение TOP используется в сочетании с операторами UNION, UNION ALL, EXCEPT и INTERSECT. Для демонстрации выполним код в листинге 8. Этот код использует операцию UNION.

USE tempdb; 
GO 
SELECT TOP(1) * FROM dbo.Inventory 
WHERE ProductName like 'Flat head%' 
UNION 
SELECT TOP(1) * FROM dbo.Inventory 
WHERE ProductName like '%socket%' 
ORDER BY Price ASC; 
GO

Листинг 8. Запрос с UNION

Намерением кода в листинге 8 было найти наименее дороге продукты “Flat head” и “socket”. При выполнении кода в листинге 8 получены результаты, показанные в отчете 8.

Отчет 8. Результаты выполнения кода в листинге 8

Код в листинге 8 не выводит наименее дорогие продукты “Flat head” или “socket”. Причина заключается в том, что, поскольку предложение ORDER BY обрабатывается после выполнения двух операторов SELECT и операции UNION.

Поскольку оба оператора SELECT возвращают несортированный набор, предложение TOP (1) просто возвращает первую строку этого неупорядоченного набора. В данном случае это не был наименее дорогой элемент. Чтобы получить результаты, необходимо обработать предложение ORDER BY для каждого оператора SELECT до выполнения предложения TOP в каждом операторе. Для правильного нахождения самых дешевых инструментов “Flat head” и “socket” может использоваться код в листинге 9.

USE tempdb; 
GO 
SELECT * FROM 
(SELECT TOP(1) * FROM dbo.Inventory 
WHERE ProductName like 'Flat head%' 
ORDER BY Price ASC) AS Flat_Head 
UNION 
SELECT * FROM 
(SELECT TOP(1) * FROM dbo.Inventory 
WHERE ProductName like '%socket%' 
ORDER BY Price ASC) AS MM; 
GO

Листинг 9. Правильное нахождение наименее дорогих продуктов

При выполнении кода в листинге 9 будут получены следующие результаты.

Отчет 9. Результаты выполнения кода в листинге 9

Добавляя предложение ORDER BY в оператор SELECT каждого подзапроса, наряду с предложением TOP, мы сначала выполняем сортировку до выборки записи TOP (1) из каждого оператора. Имея отсортированные данные, для каждого типа продуктов выбрается корректный самый дешевый пункт.

Использование предложения TOP в операторе SELECT

Предложение TOP может использоваться для ограничения числа возвращаемых строк из оператора SELECT. Число возвращаемых строк может быть точным числом или процентным отношением. Опция WITH TIES может использоваться для возврата большего числа строк, чем указано в предложении TOP, когда последняя возвращаемая строка имеет то же значение, что и несколько последующих строк. Если требуется выбрать строки на основе некоторого порядка, то должен быть включено предложение ORDER BY. Примеры в данной статье относятся только к использованию предложения TOP в операторе SELECT. Предложение TOP может также применяться к операторам INSERT, UPDATE, DELETE или MERGE. За более подробной информацией обратитесь к документации Microsoft.

Ссылки по теме

1. Советы по производительности оператора SQL TOP
2. SQL ORDER BY: 5 правил сортировки на профессиональном уровне
3. Любопытный случай сортировки с Top N
4. Получение максимального значения на группу в SQL Server: Row Number против Cross Apply