FirebirdSQL logo
 COMMENTSОператоры процедурного SQL (PSQL) 
Примеры
SELECT genus, species
FROM mammals
WHERE family = 'Felidae'
ORDER BY genus;

SELECT *
FROM persons
WHERE birthyear IN (1880, 1881)
   OR birthyear BETWEEN 1891 AND 1898;

SELECT name, street, borough, phone
FROM schools s
WHERE EXISTS (SELECT * FROM pupils p WHERE p.school = s.id)
ORDER BY borough, street;

SELECT *
FROM employees
WHERE salary >= 10000 AND position <> 'Manager';

SELECT name
FROM wrestlers
WHERE region = 'Europe'
  AND weight > ALL (SELECT weight FROM shot_putters
                    WHERE region = 'Africa');

SELECT id, name
FROM players
WHERE team_id = (SELECT id FROM teams
                 WHERE name = 'Buffaloes');

SELECT SUM (population)
FROM towns
WHERE name LIKE '%dam'
  AND province CONTAINING 'land';

SELECT pass
FROM usertable
WHERE username = current_user;

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

Предположим у вас есть таблица, в которой находятся несколько детских имён и количество шариков, которыми они обладают.

CHILD MARBLES

Anita

23

Bob E.

12

Chris

<null>

Deirdre

1

Eve

17

Fritz

0

Gerry

21

Hadassah

<null>

Isaac

6

Первое, обратите внимание на разницу между NULL и 0.Известно, что Fritz не имеет шариков вовсе, однако неизвестно количество шариков у Chris и Hadassah.

Теперь, если ввести этот SQL оператор:

SELECT LIST(child) FROM marbletable WHERE marbles > 10

вы получите имена Anita, Bob E., Eve и Gerry.Все эти дети имеют более чем 10 шариков.

Если вы отрицаете выражение:

SELECT LIST(child) FROM marbletable WHERE NOT marbles > 10

запрос вернёт Deirdre, Fritz и Isaac.Chris и Hadassah не будут включены в выборку, так как не известно 10 у них шариков или меньше.Если вы измените последний запрос так:

SELECT LIST(child) FROM  marbletable WHERE marbles <= 100

результат будет тем же самым, поскольку выражение NULL <= 10 даёт UNKNOWN.Это не то же самое что TRUE, поэтому Chris и Hadassah не отображены.Если вы хотите что бы в списке были перечислены все "бедные" дети, то измените запрос следующим образом:

SELECT LIST(child)
FROM marbletable
WHERE marbles <= 10 OR marbles IS NULL

Теперь условие поиска становится истинным для Chris и Hadassah, потому что условие “marbles is null” возвращает TRUE в этом случае.Фактически, условие поиска не может быть NULL ни для одного из них.

Наконец, следующие два примера SELECT запросов с параметрами в условии поиска.Как определяются параметры запроса и возможно ли это, зависит от приложения.Обратите внимание, что запросы подобные этим не могут быть выполнены немедленно, они должны быть предварительно подготовлены.После того как параметризованный запрос был подготовлен, пользователь (или вызывающий код) может подставить значения параметров и выполнить его многократно, подставляя перед каждым вызовом новые значения параметров.Как вводятся значения параметров, и проходят ли они предварительную обработку зависит от приложения.В GUI средах пользователь, как правило, вводит значения параметров через одно и более текстовых полей, и щелкает на кнопку "Execute", "Run" или "Refresh".

SELECT name, address, phone
FROM stores
WHERE city = ? AND class = ?

SELECT *
FROM pants
WHERE model = :model AND size = :size AND color = :col

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

GROUP BY

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

Если список выборки содержит только агрегатные столбцы или столбцы, значения которых не зависит от отдельных строк основного множества, то предложение GROUP BY необязательно.Когда предложение GROUP BY опущено, результирующее множество будет состоять из одной строки (при условии, что хотя бы один агрегатный столбец присутствует).

Если в списке выборки содержатся как агрегатные столбцы, так и столбцы, чьи значения зависит от выбираемых строк, то предложение GROUP BY становится обязательным.

Синтаксис
SELECT ...
FROM ...
GROUP BY <grouping-item> [, <grouping-item> ...]
[HAVING <grouped-row-condition>] ...

<grouping-item> ::= <non-aggr-select-item> | <non-aggr-expression>

<non-aggr-select-item> ::=
    column-copy
  | column-alias
  | column-position
Table 1. Параметры предложения GROUP BY
Параметр Описание

non-aggr-expression

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

column-copy

Дословная копия выражения из списка выбора, не содержащего агрегатной функции.

column-alias

Псевдоним выражения (столбца) из списка выбора, не содержащего агрегатной функции.

column-position

Номер позиции выражения (столбца) из списка выбора, не содержащего агрегатной функции.

Общее правило гласит, что каждый не агрегированный столбец в SELECT списке, должен быть так же включён в GROUP BY список.Вы можете это сделать тремя способами:

  1. Копировать выражение дословно из списка выбора, например “class” или “'D:' || upper(doccode)”;

  2. Указать псевдоним, если он существует;

  3. Задать положение столбца в виде целого числа, которое находится в диапазоне от 1 до количества столбцов в списке SELECT. Целые значения, полученные из выражений, параметров или просто инварианты будут использоваться в качестве таковых в группировке. Они не будут иметь никакого эффекта, поскольку их значение одинаково для каждой строки.

Important

Если вы группируете по позиции столбца или алиасу, то выражение соответствующее этой позиции (алиасу) будет скопировано из списка выборки SELECT.Это касается и подзапросов, таким образом, подзапрос будет выполняться, по крайней мере, два раза.

В дополнении к требуемым элементам, список группировки так же может содержать:

  • Столбцы исходной таблицы, которые не включены в список выборки SELECT, или неагрегатные выражения, основанные на таких столбцах. Добавление таких столбцов может дополнительно разбить группы. Но так как эти столбцы не в списке выборки SELECT, вы не можете сказать, какому значению столбца соответствует значение агрегированной строки. Таким образом, если вы заинтересованы в этой информации, вы так же должны включить этот столбец или выражение в список выборки SELECT, что возвращает вас к правилу "каждый не агрегированный столбце в списке выборки SELECT должен быть включён в список группировки `GROUP BY`";

  • Выражения, которые не зависят от данных из основного набора, т.е. константы, контекстные переменные, некоррелированные подзапросы, возвращающие единственное значение и т.д. Это упоминается только для полноты картины, т.к. добавление этих элементов является абсолютно бессмысленным, поскольку они не повлияют на группировку вообще. "Безвредные, но бесполезные" элементы так же могут фигурировать в списке выбора SELECT без их копирования в список группировки GROUP BY.

Примеры

Когда в списке выбора SELECT содержатся только агрегатные столбцы, предложение GROUP BY необязательно:

SELECT COUNT(*), AVG(age)
FROM students
WHERE sex = 'M'

Этот запрос вернёт одну строку с указанием количества студентов мужского пола и их средний возраст.Добавление выражения, которое не зависит от строк таблицы STUDENTS, ничего не меняет:

SELECT COUNT(*), AVG(age), current_date
FROM students
WHERE sex = 'M'

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

Тем не менее в обоих приведённых выше примерах это разрешено.Это совершенно справедливо и для запроса:

SELECT COUNT(*), AVG(age)
FROM students
WHERE sex = 'M'
GROUP BY class

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

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

SELECT class, COUNT(*), AVG(age)
FROM students
WHERE sex = 'M'
GROUP BY class

Теперь у нас есть полезный запрос.Обратите внимание, что добавление столбца CLASS делает предложение GROUP BY обязательным.Мы не можем удалить это предложение, так же мы не можем удалить столбец CLASS из списка столбцов.

Результат последнего запроса будет выглядеть примерно так:

CLASS COUNT AVG

2A

12

13.5

2B

9

13.9

3A

11

14.6

3B

12

14.4

…​

…​

…​

Заголовки “COUNT” и “AVG” не очень информативны.В простейшем случае вы можете обойти это, но лучше, если мы дадим им значимые имена с помощью псевдонимов:

SELECT
    class,
    COUNT(*) AS num_boys,
    AVG(age) AS boys_avg_age
FROM students
WHERE sex = 'M'
GROUP BY class

Как вы помните из формального синтаксиса списка столбцов, ключевое слово AS не является обязательным.

Добавление большего не агрегированных (или точнее строчно зависимых) столбцов требуется добавления их в предложения GROUP BY тоже.Например, вы хотите видеть вышеуказанную информацию о девочках то же, и хотите видеть разницу между интернатами и студентами дневного отделения:

SELECT
    class,
    sex,
    boarding_type,
    COUNT(*) AS anumber,
    AVG(age) AS avg_age
FROM students
GROUP BY class, sex, boarding_type
CLASS SEX BOARDING_TYPE ANUMBER AVG_AGE

2A

F

BOARDING

9

13.3

2A

F

DAY

6

13.5

2A

M

BOARDING

7

13.6

2A

M

DAY

5

13.4

2B

F

BOARDING

11

13.7

2B

F

DAY

5

13.7

2B

M

BOARDING

6

13.8

…​

…​

…​

…​

…​

Каждая строка в результирующем наборе соответствует одной конкретной комбинации переменных CLASS, SEX и BOARDING_TYPE.Агрегированные результаты — количество и средний возраст — приведены для каждой из конкретизированной группы отдельно.В результате запроса вы не можете увидеть обобщённые результаты для мальчиков отдельно или для студентов дневного отделения отдельно.Таким образом, вы должны найти компромисс.Чем больше вы добавляете неагрегатных столбцов, тем больше вы конкретизируете группы, и тем больше вы упускаете общую картину из виду.Конечно, вы все ещё можете получить “большие” агрегаты, с помощью отдельных запросов.