Проанализировав эти результаты, видим, что результаты теста 70 для TEST1 и 90 для TEST2 являются взаимообратными (существуют результат 90 для TEST1 и результат 70 для TEST2). Аналогично результаты 80

Обсуждение

DB2, MySQL, PostgreSQL и SQL Server

Для данных продуктов предлагаются два решения. В первом условие OR располагается в конструкции JOIN, что делает его частью условия объединения. Тем самым можно фильтровать строки, извлекаемые из таблицы EMP, не теряя отделы с DEPTNO 30 и 40 из таблицы DEPT.

Во втором решении фильтрация перенесена во вложенный запрос. Он фильтрует записи по EMP.DEPTNO и возвращает интересующие нас строки EMP. Затем осуществляется их внешнее объединение с DEPT. Поскольку DEPT находится в левой части левостороннего внешнего объединения, возвращены будут все отделы, включая 30 и 40.

Oracle

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

Выявление строк со взаимообратными значениями Задача

Имеется таблица, содержащая результаты двух тестов, и требуется найти пары взаимообратных значений. Рассмотрим результирующее множество, возвращаемое представлением V:

Решение

Используя рефлексивное объединение, выберите строки, в которых значения TEST1 равны значениям TEST2 и наоборот:

select distinct v1. *

from V v1, V v2

where v1.test1 = v2.test2

and v1.test2 = v2.test1

and v1.test1 <= v1.test2

Обсуждение

В результате рефлексивного объединения получаем декартово произведение, в котором каждый результат TEST1 можно сравнить с каждым результатом TEST2 и наоборот. Представленный ниже запрос выявит строки со взаимообратными значениями:

select v1.*

from V v1, V v2 where v1.test1 = v2.test2 and v1.test2 = v2.test1

TEST1 TEST2

20 20

20 20

20 20

20 20

для TEST1 и 130 для TEST2 являются взаимообратными для результатов 130 теста TEST1 и 80 теста TEST2. Кроме того, результаты 20 теста TEST1 и 20 теста TEST2 взаимообратные для результатов 20 теста TEST2 и 20 теста TEST1. Необходимо выбрать только один набор взаимообратных значений. Должно быть получено следующее результирующее множество:

TEST1 TEST2

20 20

70 90

80 130

но не такое:

В данном решении выбираются сотрудники с пятью самыми высокими заработными платами, а не пять самых высокооплачиваемых сотрудников, поэтому в результирующем наборе может быть более пяти записей. Если поставить задачу выбрать именно пять первых записей, то в SQL Server можно воспользоваться выражением TOP(5), а в MySQL и PostgreSQL -LIMIT 5. - Примеч. науч.ред.

90 70

130 80

70 90

80 130

Применение ключевого слова DISTINCT гарантирует удаление дублирующихся строк из окончательного результирующего множества. Последний фильтр предиката WHERE (and V1 .TEST1 <= V1.TEST2) обеспечит возвращение только одной пары взаимообратных значений (в которой значение TEST1 меньше или равно значению TEST2).

Как выбрать записи с n-ым количеством наивысших значений

Задача

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

Решение

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

DB2, Oracle и SQL Server

Решение данной задачи зависит от использования ранжирующей функции. То, какая ранжирующая функция будет применяться, определяется тем, как должны обрабатываться связи. В следующем решении используется функция DENSE RANK, таким образом, все дублирующиеся значения заработной платы будут рассматриваться как одно значение:

1 select ename,sal

2 from (

3 select ename, sal,

4 dense rank() over (order by sal desc) dr

5 from emp

6 ) x

7 where dr <= 5