Заключительный шаг - применить к полученному рангу функцию вычисления остатка от деления, чтобы пропустить определенные строки.

Использование логики OR во внешних объединениях Задача

Требуется получить для каждого служащего 10 и 20-го отделов информацию об его имени и отделе, а также информацию об отделе (без личной информации) для служащих 30 и 40-го отделов. Сделаем первую попытку решить эту задачу:

select e.ename, d.deptno, d.dname, d.loc

from dept d, emp e where d.deptno = e.deptno

and (e.deptno = 10 or e.deptno = 20) order by 2

ENAME DEPTNO DNAME LOC

CLARK 10 ACCOUNTING NEW YORK

KING 10 ACCOUNTING NEW YORK

MILLER 10 ACCOUNTING NEW YORK

MySQL и PostgreSQL

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

select a.ename,

(select count(*) from emp b where b.ename <= a.ename) as rn from emp a

SMITH ADAMS

FORD

SCOTT JONES

20 RESEARCH

20 RESEARCH

20 RESEARCH

20 RESEARCH

20 RESEARCH

DALLAS DALLAS DALLAS DALLAS DALLAS

Поскольку в данном запросе реализовано внутреннее объединение, в результирующее множество не вошла информация о 30 и 40-м отделах (DEPTNO 30 и 40).

В следующем запросе делается попытка внешнего объединения таблицы EMP с таблицей DEPT, но по-прежнему получается неверный результат:

select e.ename, d.deptno, d.dname, d.loc from dept d left join emp e on (d.deptno = e.deptno) where e.deptno = 10 or e.deptno = 20 order by 2

Решение

DB2, MySQL, PostgreSQL и SQL Server

Перенесите условие OR (ИЛИ) в оператор JOIN:

1 select e.ename, d.deptno, d.dname, d.loc

2 from dept d left join emp e

3 on (d.deptno = e.deptno

4 and (e.deptno=10 or e.deptno=20))

5 order by 2

Альтернативный вариант: можно сначала во вложенном запросе провести фильтрацию по EMP.DEPTNO, а затем осуществить внешнее объединение:

1 select e.ename, d.deptno, d.dname, d.loc

2 from dept d

3 left join

4 (select ename, deptno

5 from emp

6 where deptno in ( 10, 20 )

7 ) e on ( e.deptno = d.deptno )

8 order by 2

Oracle

При работе с Oracle 9i Database или более поздними версиями можно использовать любое решение для продуктов других производителей. Для остальных версий решение строится на выражениях CASE или DECODE. Далее представлено решение с применением CASE:

select e.ename, d.deptno, d.dname, d.loc

from dept d, emp e where d.deptno = e.deptno (+)

and d.deptno = case when e.deptno(+) = 10 then e.deptno(+) when e.deptno(+) = 20 then e.deptno(+)

order by 2

И вот то же решение, но на этот раз с использованием DECODE:

select e.ename, d.deptno, d.dname, d.loc

from dept d, emp e where d.deptno = e.deptno (+)

and d.deptno = decode(e.deptno(+),10,e.deptno(+),

20,e.deptno(+))

order by 2

Если применить собственный синтаксис Oracle для внешнего объединения (+) в сочетании с предикатами IN или OR к столбцу, участвующему во внешнем объединении, запрос возвратит ошибку. Выход из ситуации - перенести предикат IN или OR во вложенный запрос:

select e.ename, d.deptno, d.dname, d.loc from dept d,

( select ename, deptno from emp where deptno in ( 10, 20 ) ) e

where d.deptno = e.deptno (+) order by 2