Как вы, наверное, уже поняли с помощью одного запроса с применением агрегатных функций нельзя получить детальные и промежуточные результаты. Такие приемы возможны только с применением встроенного языка программирования PL/SQL, к которому мы уже почти подобрались и как только разделаемся с SELECT - так как без него никуда, возьмемся за это дело. Тем не менее, в сервере MS SQL существует выражение COMPUTE, которое по своей сути подрывает все основы построения реляционных запросов. Но с его помощью, возможно получение такого рода результатов не прибегая к написанию хранимых процедур. Но это так к слову и для информации о том, что мелко мягкие вообще любят подрывать основы .... :) Так же на запросы с группировкой накладывается ряд ограничений. Например, столбцы с группировкой должны представлять собой реальные столбцы таблиц. Нельзя группировать строки на основе вычисляемого выражения. Так же существуют ограничения на элементы возвращаемых значений. Возвращаемым столбцом может быть:
Обычно в список возвращаемых столбцов запросов с группировкой входят столбец группировки и агрегатная функция. Если не указать агрегат, то можно просто обойтись выражением DISTINCT без использования предложения GROUP BY! А так же, если в запрос не включить столбец группировки, вы не сможете определить, к какой именно группе относится та или иная строка результата запроса! Так же в SQL92 игнорируется информация о первичных и вторичных ключах, при анализе запроса с группировкой.
Вот, например: Подсчитать общее количество заказов для каждого служащего.
SELECT EMPL_NUM, NAME, SUM(AMOUNT) FROM ORDERS, SALESREPS WHERE REP = EMPL_NUM GROUP BY EMPL_NUM /Уууупс! А вот и ошибочка!
SQL> SELECT EMPL_NUM, NAME, SUM(AMOUNT) 2 FROM ORDERS, SALESREPS 3 WHERE REP = EMPL_NUM 4 GROUP BY EMPL_NUM 5 / SELECT EMPL_NUM, NAME, SUM(AMOUNT) FROM ORDERS, SALESREPS WHERE REP = EMPL_NUM GROUP BY EMPL_NUM ORA-00979: выражение не является выражением GROUP BY
В данном случае имеется в виду поле NAME. Так как оно явно не вписывается в запросе! Хотя, если рассуждать с точки зрения природы данных, все вроде бы правильно, но не совсем. Столбец EMPL_NUM является первичным ключом таблицы SALESREPS, поэтому столбец NAME должен иметь одно значение для каждой группы! Правильно, просто нужно указать этот столбец в выражении группировки вот так: Подсчитать общее количество заказов для каждого служащего.
SELECT EMPL_NUM, NAME, SUM(AMOUNT) FROM ORDERS, SALESREPS WHERE REP = EMPL_NUM GROUP BY EMPL_NUM, NAME /
Вот теперь правильно! :)
SQL> SELECT EMPL_NUM, NAME, SUM(AMOUNT) 2 FROM ORDERS, SALESREPS 3 WHERE REP = EMPL_NUM 4 GROUP BY EMPL_NUM, NAME 5 / EMPL_NUM NAME SUM(AMOUNT) ------ ------------------------------ ----------- 101 Игорь Николаев 176,478 102 Филип Киркоров 22,776 103 Дима Маликов 602,44 105 Вася Пупкин 740,625 106 Света Разина 32,958 107 Маша Сидорова 685,78 108 Игорь Петров 2178,445 109 Маша Распутина 7,103 110 Крис Кельми 654,5 9 rows selected
А можно сделать еще проще. Вот так: Подсчитать общее количество заказов для каждого служащего.
SELECT NAME, SUM(AMOUNT) FROM ORDERS, SALESREPS WHERE REP = EMPL_NUM GROUP BY NAME /
Получаем:
SQL> SELECT NAME, SUM(AMOUNT) 2 FROM ORDERS, SALESREPS 3 WHERE REP = EMPL_NUM 4 GROUP BY NAME 5 / NAME SUM(AMOUNT) ------------------------------ ----------- Вася Пупкин 740,625 Дима Маликов 602,44 Игорь Николаев 176,478 Игорь Петров 2178,445 Крис Кельми 654,5 Маша Распутина 7,103 Маша Сидорова 685,78 Света Разина 32,958 Филип Киркоров 22,776 9 rows selected
Теперь надеюсь, все стало ясно с группировками, если что не понятно можете писать письма! :) А вот теперь давайте вернемся к нашему старому знакомому - ну, конечно же, NULL. Что будет, если в одном из полей группировки будет присутствовать NULL? К какой группе его отнести? В предложении WHERE по правилам сравнение NULL и NULL скажем на равенство не дает TRUE, а будет равно NULL! В предложении GROUP BY это крайне не удобно, так как каждый NULL будет генерировать новую группу. Поэтому в стандарте ANSI/ISO определено, что в предложении GROUP BY значения NULL РАВНЫ!!! Следовательно, он не будут вносить неразбериху в запросах с группировкой! Что, собственно и требовалось в данном случае. Для примера создадим табличку COLORIS и проверим наши рассуждения.
Создаем:
CREATE TABLE COLORIS ( NM VARCHAR2(50), HAIR VARCHAR2(50), EYES VARCHAR2(50) ) / SQL> CREATE TABLE COLORIS 2 ( 3 NM VARCHAR2(50), 4 HAIR VARCHAR2(50), 5 EYES VARCHAR2(50) 6 ) 7 / Table created
Табличка создана! Не забудьте дать оператор COMMIT вот так:
COMMIT /
Если все верно, то получите следующее:
SQL> COMMIT 2 /
Теперь операцией копирования через буфер обмена отправьте в SQLPlus следующее:
INSERT INTO COLORIS(NM, HAIR, EYES)
VALUES('Cindy', 'Brown', 'Blue')
/
INSERT INTO COLORIS(NM, HAIR, EYES)
VALUES('Louise', NULL, 'Blue')
/
INSERT INTO COLORIS(NM, HAIR, EYES)
VALUES('Harry', NULL, 'Blue')
/
INSERT INTO COLORIS(NM, HAIR, EYES)
VALUES('Samantha', NULL, NULL)
/
INSERT INTO COLORIS(NM, HAIR, EYES)
VALUES('Joanne', NULL, NULL)
/
INSERT INTO COLORIS(NM, HAIR, EYES)
VALUES('George', 'Brown', NULL)
/
INSERT INTO COLORIS(NM, HAIR, EYES)
VALUES('Mary', 'Brown', NULL)
/
INSERT INTO COLORIS(NM, HAIR, EYES)
VALUES('Paula', 'Brown', NULL)
/
INSERT INTO COLORIS(NM, HAIR, EYES)
VALUES('Kevin', 'Brown', NULL)
/
INSERT INTO COLORIS(NM, HAIR, EYES)
VALUES('Joel', 'Brown', 'Brown')
/
INSERT INTO COLORIS(NM, HAIR, EYES)
VALUES('Susan', 'Blonde', 'Blue')
/
INSERT INTO COLORIS(NM, HAIR, EYES)
VALUES('Marie', 'Blonde', 'Blue')
/
COMMIT
/
Если все успешно прошло получите примерно следующее:
SQL> INSERT INTO COLORIS(NM, HAIR, EYES)
2 VALUES('Cindy', 'Drown', 'Blue')
3 /
1 row inserted
.
.
.
.
SQL> INSERT INTO COLORIS(NM, HAIR, EYES)
2 VALUES('Marie', 'Blonde', 'Blue')
3 /
1 row inserted
SQL> commit
2 /
Commit complete
Табличка заполнена данными! Как мы это проделали, напомню, применив оператор INSERT. Он отправляет данные в таблицы БД. О нем мы еще поговорим в дальнейшем. А вот теперь давайте наконец дадим запрос и проверим теорию равенства NULL в выражениях GROUP BY:
SELECT HAIR, EYES, COUNT(*) FROM COLORIS GROUP BY HAIR, EYES /
Получаем:
SQL> SELECT HAIR, EYES, COUNT(*) 2 FROM COLORIS 3 GROUP BY HAIR, EYES 4 / HAIR EYES COUNT(*) ------- ------- -------- Blonde Blue 2 Brown Blue 1 Brown Brown 1 Brown NULL 4 NULL Blue 2 NULL NULL 2
Хорошо видно, как NULL сформировал собственную группу! Вот теперь кажется разобрались в особенностях работы агрегатов со значениями NULL в БД Oracle. :)