1 select dept.deptno,

2 case dept.deptno

3 when 10 then emp cnts.deptno 10

4 when 20 then emp cnts.deptno 20

5 when 30 then emp cnts.deptno 30

6 end as counts by dept

7 from (

8 select sum(case when deptno=10 then 1 else 0 end) as deptno 10,

9 sum(case when deptno=20 then 1 else 0 end) as deptno 20,

10 sum(case when deptno=30 then 1 else 0 end) as deptno 30

11 from emp

12 ) emp cnts,

13 (select deptno from dept where deptno <= 30) dept

Обсуждение

Вложенный запрос EMP CNTS является денормализованным представлением, или широким результирующим множеством, которое требуется преобразовать в строки. Оно показано ниже:

select sum(case when deptno=10 then 1 else 0 end) as deptno 10,

sum(case when deptno=20 then 1 else 0 end) as deptno 20,

sum(case when deptno=30 then 1 else 0 end) as deptno 30 from emp

DEPTNO 10 DEPTNO 20 DEPTNO 30 3 5 6

Поскольку здесь три столбца, будет создано три строки. Начнем с декартова произведения между вложенным запросом EMP CNTS и некоторым табличным выражением, имеющим, по крайней мере, три строки. В следующем коде для создания декартова произведения используется таблица DEPT. В DEPT четыре строки:

select dept.deptno,

emp cnts.deptno 10, emp cnts.deptno 20, emp cnts.deptno 30 from (

select sum(case when deptno=10 then 1 else 0 end) as deptno 10, sum(case when deptno=20 then 1 else 0 end) as deptno 20, sum(case when deptno=30 then 1 else 0 end) as deptno 30 from emp

) emp cnts,

(select deptno from dept where deptno <= 30) dept

DEPTNO DEPTNO 10 DEPTNO 20 DEPTNO 30

10 3 5 6

20 3 5 6

30 3 5 6

Декартово произведение позволяет получить по строке для каждого столбца вложенного запроса EMP CNTS. Поскольку в окончательное результирующее множество должны войти только значения DEPTNO и количество служащих в соответствующем DEPTNO, используем выражение CASE для преобразования трех столбцов в один:

select dept.deptno,

case dept.deptno

when 10 then emp cnts.deptno 10 when 20 then emp cnts.deptno 20 when 30 then emp cnts.deptno 30 end as counts by dept from (

select sum(case when deptno=10 then 1 else 0 end) as deptno 10, sum(case when deptno=20 then 1 else 0 end) as deptno 20, sum(case when deptno=30 then 1 else 0 end) as deptno 30 from emp

) emp cnts,

(select deptno from dept where deptno <= 30) dept

DEPTNO COUNTS BY DEPT

10 3

20 5

30 6

Обратное разворачивание результирующего множества в один столбец

Задача

Требуется вывести все возвращаемые запросом столбцы в одном столбце. Например, стоит задача получить ENAME, JOB и SAL всех служащих 10-го отдела (DEPTNO 10), все три значения должны быть выведены в одном столбце в трех строках для каждого служащего, и значения для разных служащих должны быть разделены пустой строкой. Ожидается получить следующее результирующее множество:

EMPS

CLARK

MANAGER

2450

KING

PRESIDENT

5000

MILLER

CLERK

1300

Решение

Ключ к решению - использовать декартово произведение и возвратить по четыре строки для каждого служащего. Это позволит разместить значения столбцов в отдельных строках и разделить значения, относящиеся к разным служащим, пустыми строками.

DB2, Oracle и SQL Server

С помощью ранжирующей функции ROW NUMBER OVER присвойте каждой строке ранг на основании значений EMPNO (1-4). Затем используйте выражение CASE для преобразования трех столбцов в один:

1 select case rn

2 when 1 then ename

3 when 2 then job

4 when 3 then cast(sal as char(4))

5 end emps

6 from (

7 select e.ename,e.job,e.sal,

8 row number()over(partition by e.empno

9 order by e.empno) rn

10 from emp e,

11 (select *

12 from emp where job=CLERK) four rows

13 where e.deptno=10

14 ) x PostgreSQL и MySQL

Данный рецепт призван обратить внимание на применение ранжирующих функций для ранжирования строк, которое затем используется при разворачивании таблицы. На момент написания данной книги ни PostgreSQL, ни MySQL не поддерживают ранжирующие функции.

Обсуждение

DB2, Oracle и SQL Server

Первый шаг - с помощью ранжирующей функции ROW NUMBER OVER присвоить ранг каждому служащему DEPTNO 10:

select e.ename,e.job,e.sal,

row number()over(partition by e.empno

order by e.empno) rn

from emp e where e.deptno=10

ENAME JOB SAL RN

CLARK MANAGER 2450 1

KING PRESIDENT 5000 1

MILLER CLERK 1300 1