Как возвратить столбцы, не перечисленные в операторе GROUP BY

Задача

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

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

DEPTNO ENAME JOB SAL DEPT STATUS JOB STATUS

Затем применяем к каждому выражению CASE агрегатную функцию MAX, группируя по RNK, чтобы удалить из результирующего множества все значения NULL. Упорядочиваем результаты по возрастанию или по убыванию, в зависимости от того, как используемая СУБД сортирует значения NULL.

select max(deptno 10) as d10, max(deptno 20) as d20, max(deptno 30) as d30 from (

select case when e.deptno=10 then else null end deptno 10, case when e.deptno=20 then * else null end deptno 20, case when e.deptno=30 then * else null end deptno 30, (select count(*) from emp d where e.deptno=d.deptno and e.empno < d.empno ) as rnk from emp e ) x

group by rnk

order by 1 desc, 2 desc, 3 desc

D10 D20 D30

К сожалению, включение всех этих столбцов в оператор SELECT разрушит группировку. Рассмотрим такой пример. Служащий KING получает самую высокую заработную плату. Мы хотим убедиться в этом с помощью следующего запроса:

select ename,max(sal) from emp group by ename

Вместо того чтобы представить запись KING и его заработную плату, приведенный выше запрос возвратит все 14 строк таблицы EMP. Причина в группировке: MAX(SAL) применяется к каждому ENAME. Таким образом, кажется, что приведенный выше запрос можно интерпретировать как найти служащего, получающего наивысшую заработную плату , а на самом деле он находит наивысшую заработную плату для каждого значения ENAME в таблице EMP . В данном рецепте рассматривается, как можно включить в результирующее множество столбец ENAME, не указывая его в операторе GROUP BY.

Решение

Наибольшую и наименьшую заработные платы по DEPTNO и JOB находим с помощью вложенного запроса. Затем выбираем только тех служащих, которые получают такие зарплаты.

DB2, Oracle и SQL Server

С помощью оконных функций MAX OVER и MIN OVER найдите наибольшую и наименьшую заработные платы по DEPTNO и JOB. Затем выберите строки, в которых заработные платы соответствуют полученным наибольшим и наименьшим значениям:

1 select deptno,ename,]ob,sal,

2 case when sal = max by dept

3 then TOP SAL IN DEPT

4 when sal = min by dept

5 then LOW SAL IN DEPT

6 end dept status,

7 case when sal = max by ]ob

8 then TOP SAL IN JOB

9 when sal = min by ]ob

10 then LOW SAL IN JOB

11 end ]ob status

12 from (

13 select deptno,ename,]ob,sal,

14 max(sal)over(partition by deptno) max by dept,

15 max(sal)over(partition by job) max by ]ob,

16 min(sal)over(partition by deptno) min by dept,

17 min(sal)over(partition by job) min by ]ob

18 from emp

19 ) emp sals

20 where sal in (max by dept,max by job,

21 min by dept,min by job)

PostgreSQL и MySQL

С помощью скалярных подзапросов найдите наибольшую и наименьшую заработные платы по DEPTNO и JOB. Затем выберите строки только тех служащих, которые получают такие заработные платы:

1 select deptno,ename,job,sal,

2 case when sal = max by dept

3 then TOP SAL IN DEPT

4 when sal = min by dept

5 then LOW SAL IN DEPT

6 end as dept status,

7 case when sal = max by job

8 then TOP SAL IN JOB

9 when sal = min by job

10 then LOW SAL IN JOB

11 end as job status

12 from (

13 select e.deptno,e.ename,e.job,e.sal,

14 (select max(sal) from emp d

15 where d.deptno = e.deptno) as max by dept,

16 (select max(sal) from emp d

17 where d.job = e.job) as max by job,

18 (select min(sal) from emp d

19 where d.deptno = e.deptno) as min by dept,

20 (select min(sal) from emp d

21 where d.job = e.job) as min by job

22 from emp e

23 ) x

24 where sal in (max by dept,max by job,

25 min by dept,min by job)

Обсуждение

DB2, Oracle и SQL Server

Первый шаг - с помощью оконных функций MAX OVER и MIN OVER находим наибольшие и наименьшие заработные платы по DEPTNO иJOB.