Сравнение строк по определенной части даты Задача

Требуется найти служащих, которые были приняты на работу в один месяц и день недели. Например, если один служащий был нанят в понедельник 10 марта 1988 года, а другой служащий - в понедельник 2 марта 2001, они должны быть включены в результирующее множество, поскольку день недели и месяц их приема на работу совпадают. В таблице EMP только трое служащих отвечают этому требованию. Должно быть получено следующее результирующее множество:

JAMES was hired on the same month and weekday as FORD

SCOTT was hired on the same month and weekday as JAMES

SCOTT was hired on the same month and weekday as FORD

Решение

Поскольку требуется сравнивать значение HIREDATE одного служащего со значениями HIREDATE других служащих, понадобится провести рефлексивное объединение таблицы EMP. Тем самым мы получим все возможные сочетания HIREDATE. После этого для каждой строки мы просто будем извлекать день недели и месяц из значений HIREDATE и сравнивать их у разных строк.

После рефлексивного объединения таблицы EMP с помощью функции DAYOFWEEK возвратите численное значение дня недели. Используйте функцию MONTHNAME, чтобы получить название месяца:

1 select a.ename

2 was hired on the same month and weekday as

3 b.ename msg

4 from emp a, emp b

5 where (dayofweek(a.hiredate),monthname(a.hiredate)) = b (dayofweek(b.hiredate),monthname(b.hiredate))

7 and a.empno < b.empno

8 order by a.ename

Oracle и PostgreSQL

После рефлексивного объединения таблицы EMP с помощью функции TO CHAR извлеките из HIREDATE день недели и месяц для проведения сравнения:

1 select a.ename

2 was hired on the same month and weekday as

3 b.ename as msg

4 from emp a, emp b

5 where to char(a.hiredate,DMON) =

Обсуждение

Решения отличаются лишь функциями работы с датами, используемыми для форматирования HIREDATE. В данном обсуждении я буду опираться на решение для Oracle/PostgreSQL (потому что оно самое лаконичное), но приводимые объяснения правомочны и для всех остальных решений.

Первый шаг - рефлексивное объединение EMP. Таким образом мы получаем все возможные комбинации значения HIREDATE заданного служащего и значений HIREDATE других служащих, что упрощает процесс сравнения. Рассмотрим приведенные ниже результаты запроса (фильтрация осуществляется по служащему SCOTT):

select a.ename as scott, a.hiredate as scott hd,

b.ename as other emps, b.hiredate as other hds from emp a, emp b where a.ename = SCOTT and a.empno != b.empno

6 to char(b.hiredate,DMON)

7 and a.empno < b.empno

8 order by a.ename

MySQL

После рефлексивного объединения таблицы EMP с помощью функции DATE FORMAT извлеките из HIREDATE день недели и месяц для проведения сравнения:

1 select concat(a.ename,

2 was hired on the same month and weekday as ,

3 b.ename) msg

4 from emp a, emp b

5 where date format(a.hiredate,%w%M) =

6 date format(b.hiredate,%w%M)

7 and a.empno < b.empno

8 order by a.ename

SQL Server

После рефлексивного объединения таблицы EMP с помощью функции DATENAME извлеките из HIREDATE день недели и месяц для проведения сравнения:

1 select a.ename +

2 was hired on the same month and weekday as +

3 b.ename msg

4 from emp a, emp b

5 where datename(dw,a.hiredate) = datename(dw,b.hiredate)

6 and datename(m,a.hiredate) = datename(m,b.hiredate)

7 and a.empno < b.empno

8 order by a.ename

Благодаря рефлексивному объединению таблицы EMP мы можем сравнивать значение HIREDATE служащего SCOTT со значениями HI-REDATE всех остальных служащих. Для EMPNO задан фильтр, который обеспечивает, что значение HIREDATE служащего SCOTT не будет включено в столбец OTHER HDS. Следующий шаг - использовать предоставляемые СУБД функции форматирования дат для сравнения значений HIREDATE по дню недели и месяцу и выбора только тех значений, которые соответствуют заданному:

select a.ename as emp1, a.hiredate as emp1 hd, b.ename as emp2, b.hiredate as emp2 hd from emp a, emp b where to char(a.hiredate,DMON) = to char(b.hiredate,DMON) and a.empno != b.empno order by 1

На данном этапе соответствующие значения HIREDATE выбраны правильно, но возвращено шесть строк вместо трех, приведенных в разделе Задача данного рецепта. Причина вывода лишних строк кроется в фильтре по EMPNO. Использование оператора не равно не обеспечивает отсеивание обратных равенств. Например, в первой строке сопоставляются FORD и SCOTT, а в последней строке - SCOTT и FORD.

Шесть строк результирующего множества, с технической точки зрения, возвращены правильно, но они дублируют друг друга. Избавиться от дублирования позволит оператор меньше чем (значения HIRE-