Материал: Базы данных и их безопасность. Плотников Д.Г

Функция во фразе select с предикатом

Выдать общее количество поставляемых деталей Р2.

SELECT SUM (КОЛИЧЕСТВО)

FROM SP

WHERE НОМЕР_ДЕТАЛИ = ’Р2’;

Результат:

1000

Функция в подзапросе

Выдать номера поставщиков со значением поля СОСТОЯНИЕ меньшим, чем текущее максимальное состояние в таблице S.

SELECT НОМЕР_ПОСТАВЩИКА

FROM S

WHERE СОСТОЯНИЕ <

(SELECT МАХ (СОСТОЯНИЕ)

FROM S);

Результат:

НОМЕР_ПОСТАВЩИКА

S1

S2

S4

Функция в коррелированном подзапросе

Выдать номер поставщика, состояние и город для всех поставщиков, у которых состояние больше или равно среднему для их конкретного города.

SELECT НОМЕР_ПОСТАВЩИКА, СОСТОЯНИЕ, ГОРОД

FROM S SX

WHERE СОСТОЯНИЕ >=

(SELECT AVG (СОСТОЯНИЕ) FROM S SY

WHERE SY.ГОРОД = SX.ГОРОД);

286

Включить в результат среднее состояние для каждого города невозможно.

Использование фразы group by

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

SELECT НОМЕР_ДЕТАЛИ, SUM (КОЛИЧЕСТВО)

FROM SP

GROUP BY НОМЕР_ДЕТАЛИ;

Результат:

НОМЕР_ДЕТАЛИ

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

287

не означает, что таблица физически перекомпоновывается в базе данных. В рассматриваемом примере строки таблицы SP группируются таким образом, что в одной группе содержатся все строки для детали Р1, в другой—все строки для детали Р2 и т. д. Далее, к каждой группе перекомпонованной таблицы, а не

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

кединственному значению.

Строки таблицы можно группировать по любой комбинации ее полей. Будет приведен пример, иллюстрирующий группирование более чем по одному полю. Заметим, что фраза GROUP BY не предполагает ORDER BY (упорядочить по). Чтобы гарантировать упорядочение результата этого примера по номерам деталей, следует специфицировать фразу ORDER BY НОМЕР_ДЕТАЛИ после фразы GROUP BY. Если поле, по значениям которого осуществляется группирование, содержит какие-либо неопределенные значения, то каждое из них порождает отдельную группу.

Использование фразы where с group by

Выдать для каждой поставляемой детали ее номер и общий объем поставок, за исключением поставок поставщика

S1:

SELECT НОМЕР_ДЕТАЛИ, SUM (КОЛИЧЕСТВО)

FROM SP

WHERE НОМЕР_ПОСТАВЩИКА != ’S1’ GROUP BY НОМЕР_ДЕТАЛИ;

288

Результат:

НОМЕР_ДЕТАЛИ

Строки, не удовлетворяющие фразе WHERE, исключаются до того, как будет осуществляться какое-либо группирование.

Использование having

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

SELECT НОМЕР_ДЕТАЛИ

FROM SP

GROUP BY НОМЕР_ДЕТАЛИ

HAVING COUNT (*) > 1;

Результат:

НОМЕР_ДЕТАЛИ

P1

P2

P4

P5

Фраза HAVING играет такую же роль для групп, что и фраза WHERE для строк. (Конечно, если специфицирована фраза HAVING, то должна быть специфицирована и фраза GROUP BY.) Иными словами, HAVING используется для того, чтобы исключать группы, точно так же, как WHERE используется для исключения строк. Выражение во фразе HAVING должно принимать единственное значение для группы.

Было показано, что этот запрос может быть

289

сформулирован без GROUP BY (и без HAVING) с использованием коррелированного подзапроса. Однако этот пример в действительности основан на несколько ином восприятии логики, связанной с определением ответа на этот вопрос. Можно также сформулировать запрос, используя по существу ту же логику, что и в варианте GROUP BY/HAVING, но без явного использования фраз GROUP BY и HAVING вообще:

SELECT DISTINCT НОМЕР_ДЕТАЛИ FROM SP SPX

WHERE 1 <

(SELECT COUNT (*) FROM SP SPY

WHERE SPY.НОМЕР_ДЕТАЛИ = SPX.HOMEP_ДЕТАЛИ);

Следующий вариант, в котором вместо SPX используется таблица Р, является, вероятно, более ясным:

SELECT НОМЕР_ДЕТАЛИ

FROM Р WHERE 1 <

(SELECT COUNT (НОМЕР_ПОСТАВЩИКА) FROM SP

WHERE НОМЕР_ДЕТАЛИ = P.НОМЕР_ДЕТАЛИ);

Еще одна формулировка связана с использованием

EXISTS:

SELECT НОМЕР_ДЕТАЛИ

FROM Р WHERE EXISTS

(SELECT * FROM SP SPX

WHERE SPX.НОМЕР_ДЕТАЛИ = P.НОМЕР_ДЕТАЛИ

290