1 select avg(sal)

2 from emp

3 where sal not in (

4 (select min(sal) from emp),

5 (select max(sal) from emp)

DB2, Oracle и SQL Server

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

1 select avg(sal)

2 from (

3 select sal, min(sal)over() min sal, max(sal)over() max sal

4 from emp

5 ) x

6 where sal not in (min sal,max sal)

Обсуждение

MySQL и PostgreSQL

Подзапросы возвращают наибольшую и наименьшую заработные платы в таблице. Применяя к возвращенным значениям оператор NOT IN, мы исключаем их из вычисления среднего. Помните, что в определении среднего не будут участвовать и все дубликаты (если наибольшую или наименьшую заработную плату получают несколько служащих). Если целью является исключить только по одному экземпляру предельных значений, они просто вычитаются из SUM, и потом осуществляется деление:

select (sum(sal)-min(sal)-max(sal))/(count(*)-2) from emp

DB2, Oracle и SQL Server

Вложенный запрос Х возвращает все заработные платы, а также наибольшую и наименьшую из них:

select sal, min(sal)over() min sal, max(sal)over() max sal from emp

Обратиться к наибольшей и наименьшей заработной плате можно в любой строке, поэтому доступ к ним не составляет труда. Внешний запрос фильтрует строки, возвращенные вложенным запросом Х, так, чтобы все заработные платы, соответствующие MIN SAL или MAX SAL, были исключены из вычисления среднего.

Преобразование буквенно-цифровых строк в числа Задача

Имеются буквенно-цифровые данные, и требуется выбрать из них только числа. Стоит задача получить число 123321 из строки paul123f321 .

Решение DB2

Для извлечения числовых символов из буквенно-цифровой строки используются функции TRANSLATE и REPLACE:

1 select cast(

2 replace(

3 translate( paul123f321,

4 repeat(#,26),

5 abcdefghijklmnopqrstuvwxyz),#,)

6 as integer ) as num

7 from t1

Oracle и PostgreSQL

Для извлечения числовых символов из буквенно-цифровой строки используются функции TRANSLATE и REPLACE:

1 select cast(

2 replace(

3 translate( paul123f321,

4 abcdefghijklmnopqrstuvwxyz,

5 rpad(#,26,#)),#, )

6 as integer ) as num

7 from t1

MySQL and SQL Server

Решение не предоставляется, поскольку на момент написания данной книги ни один из этих производителей не поддерживает функцию

TRANSLATE.

####123#321

Поскольку все нечисловые символы теперь представлены символом # , просто удаляем их с помощью функции REPLACE. Затем приводим результат к числовому типу. Этот конкретный пример предельно прост, потому что представлены только буквенно-цифровые данные. Если могут храниться и другие символы, проще не вылавливать их, а подойти к решению этой задачи по-другому: вместо того, чтобы искать и удалять нечисловые символы, выбирайте все числовые символы и удаляйте все остальные. Следующий пример поясняет такой подход:

select replace(

translate(paul123f321,

replace(translate( paul123f321 0123456789,

rpad(#,10,#)),#,), rpad(#,length(paul123f321),#)),#, ) as num

from t1

123321

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

select translate( paul123f321, 0123456789,

rpad(#,10,#))

from t1

TRANSLATE(

paul###f###

Обсуждение

Два решения отличаются только синтаксисом: для DB2 используется функция REPEAT, а не RPAD, и порядок параметров в списке функции TRANSLATE разный. Данное обсуждение ориентируется на пример решения для Oracle/PostgreSQL, но оно правомочно и для DB2. Если выполнить запрос с изнанки (начиная с TRANSLATE), станет видно, что все очень просто. Сначала TRANSLATE заменяет все нечисловые символы символом # :

select translate( paul123f321,

abcdefghijklmnopqrstuvwxyz, rpad(#,26,#)) as num

from t1