double arrow

Использование агрегатных функций в запросах

Таблица 5 Отношение CD (Поставки)

Таблица 4 Отношение DETAILS (Детали)

Таблица 3 Отношение CONTRAGENTS

Таблица 2 Отношение D (Детали)

Таблица 1 Отношение P (Поставщики)

DNUM DNAME DSTATUS
Болт
Гайка
Винт

Ответ на вопрос "какие поставщики имеют право поставлять какие детали?" дает следующий запрос:

SELECT P.PNUM, P.PNAME, P.PSTATUS, D.DNUM, D.DNAME, D.DSTATUS FROM P, D WHERE P.PSTATUS >= D.DSTATUS;

В результате получим следующую таблицу:

PNUM PNAME PSTATUS DNUM DNAME DSTATUS
Иванов Болт
Иванов Гайка
Иванов Винт
Петров Винт
Сидоров Гайка
Сидоров Винт

Использование имен корреляции (алиасов, псевдонимов)

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

Пример 19. Отобрать все пары поставщиков таким образом, чтобы первый поставщик в паре имел статус, больший статуса второго поставщика:

SELECT P1.PNAME AS PNAME1, P1.PSTATUS AS PSTATUS1, P2.PNAME AS PNAME2, P2.PSTATUS AS PSTATUS2 FROM P P1, P P2 WHERE P1.PSTATUS1 > P2.PSTATUS2;

В результате получим следующую таблицу:

PNAME1 PSTATUS1 PNAME2 PSTATUS2
Иванов Петров
Иванов Сидоров
Сидоров Петров

Пример 20. Рассмотрим ситуацию, когда некоторые поставщики (назовем их контрагенты) могут выступать как в качестве поставщиков деталей, так и в качестве получателей. Таблицы, хранящие данные могут иметь следующий вид:

Номер контрагента NUM Наименование контрагента NAME
Иванов
Петров
Сидоров
Номер детали DNUM Наименование детали DNAME
Болт
Гайка
Винт
Номер поставщика PNUM Номер получателя CNUM Номер детали DNUM Поставляемое количество VOLUME

В таблице CD (поставки) поля PNUM и CNUM являются внешними ключами, ссылающимися на потенциальный ключ NUM в таблице CONTRAGENTS.

Ответ на вопрос "кто кому что в каком количестве поставляет" дается следующим запросом:

SELECT P.NAME AS PNAME, C.NAME AS CNAME, DETAILS.DNAME, CD.VOLUME FROM CONTRAGENTS P, CONTRAGENTS C, DETAILS, CD WHERE P.NUM = CD.PNUM AND C.NUM = CD.CNUM AND D.DNUM = CD.DNUM;

В результате получим следующую таблицу:

Наименование поставщика PNAME Наименование получателя CNAME Наименование детали DNAME Поставляемое количество VOLUME
Иванов Петров Болт
Иванов Сидоров Гайка
Иванов Сидоров Винт
Петров Сидоров Болт
Петров Сидоров Гайка
Сидоров Иванов Болт

Замечание. Этот же запрос может быть выражен очень большим количеством способов, например, так:

SELECT P.NAME AS PNAME, C.NAME AS CNAME, DETAILS.DNAME, CD.VOLUME FROM CONTRAGENTS P, CONTRAGENTS C, DETAILS NATURAL JOIN CD WHERE P.NUM = CD.PNUM AND C.NUM = CD.CNUM;

Пример 21. Получить общее количество поставщиков (ключевое слово COUNT):

SELECT COUNT(*) AS N FROM P;

В результате получим таблицу с одним столбцом и одной строкой, содержащей количество строк из таблицы P:

N

Пример 22. Получить общее, максимальное, минимальное и среднее количества поставляемых деталей (ключевые слова SUM, MAX, MIN, AVG):

SELECT SUM(PD.VOLUME) AS SM, MAX(PD.VOLUME) AS MX, MIN(PD.VOLUME) AS MN, AVG(PD.VOLUME) AS AV FROM PD;

В результате получим следующую таблицу с одной строкой:

SM MX MN AV
333.33333333

Сейчас читают про: