SQL 比较特定的日期要素

SQL 比较特定的日期要素，你想找出哪些员工在同一个月份和同一个工作日入职。例如，一个员工的入职日期是 1988 年 3 月 10 日，星期一；另一个则是 2001 年 3 月 2 日，星期一。那么，由于二者的月份名称和星期值一致，你认为他们两个的入职日期是相匹配的。对于 EMP 表而言，只有 3 个员工符合这种条件。你希望得到的结果集如下所示。

SQL 比较特定的日期要素问题描述

你想找出哪些员工在同一个月份和同一个工作日入职。例如，一个员工的入职日期是 1988 年 3 月 10 日，星期一；另一个则是 2001 年 3 月 2 日，星期一。那么，由于二者的月份名称和星期值一致，你认为他们两个的入职日期是相匹配的。对于 EMP 表而言，只有 3 个员工符合这种条件。你希望得到的结果集如下所示。

MSG
-------------------------------------------------------
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

SQL 比较特定的日期要素解决方案

我们希望把一个员工的 HIREDATE 与另一个员工的 HIREDATE 相比较，因而需要对 EMP 表做自连接查询。这样每一种可能的 HIREDATE 组合都会被拿出来比较一下。然后，只需提取出每一个 HIREDATE 的星期值和月份并做比较即可。
DB2
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)) =
6        (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') =
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

SQL 比较特定的日期要素扩展知识

以上几种解决方案只在格式化 HIREDATE 时用到了不同的日期函数。下面的讨论部分将以 Oracle 和 PostgreSQL 的解决方案为例（因为该解决方案最为简短）；尽管如此，下面的讲解内容也同样适用于其他解决方案。
首先用 EMP 表做自连接查询，这样每个员工都能访问其他员工的 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
　
SCOTT      SCOTT_HD    OTHER_EMPS OTHER_HDS
---------- ----------- ---------- -----------
SCOTT      09-DEC-1982 SMITH      17-DEC-1980
SCOTT      09-DEC-1982 ALLEN      20-FEB-1981
SCOTT      09-DEC-1982 WARD       22-FEB-1981
SCOTT      09-DEC-1982 JONES      02-APR-1981
SCOTT      09-DEC-1982 MARTIN     28-SEP-1981
SCOTT      09-DEC-1982 BLAKE      01-MAY-1981
SCOTT      09-DEC-1982 CLARK      09-JUN-1981
SCOTT      09-DEC-1982 KING       17-NOV-1981
SCOTT      09-DEC-1982 TURNER     08-SEP-1981
SCOTT      09-DEC-1982 ADAMS      12-JAN-1983
SCOTT      09-DEC-1982 JAMES      03-DEC-1981
SCOTT      09-DEC-1982 FORD       03-DEC-1981
SCOTT      09-DEC-1982 MILLER     23-JAN-1982

通过 EMP 表自连接查询，我们把 SCOTT 的 HIREDATE 和所有其他员工的 HIREDATE 做了比较。筛选条件里的 EMPNO 表明 SCOTT 的 HIREDATE 不会作为 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
　
EMP1       EMP1_HD     EMP2       EMP2_HD
---------- ----------- ---------- -----------
FORD       03-DEC-1981 SCOTT      09-DEC-1982
FORD       03-DEC-1981 JAMES      03-DEC-1981
JAMES      03-DEC-1981 SCOTT      09-DEC-1982
JAMES      03-DEC-1981 FORD       03-DEC-1981
SCOTT      09-DEC-1982 JAMES      03-DEC-1981
SCOTT      09-DEC-1982 FORD       03-DEC-1981

现在，HIREDATE 都已经正确地匹配出来了，但是我们得到了 6 行查询结果，而不是前面讲过的 3 行。这是因为筛选条件 EMPNO 导致了多余的行。使用“不等于”作为筛选条件，我们就没有办法过滤掉反向的查询结果。例如，上述第一行是 FORD 匹配 SCOTT 的结果，而最后一行则是 SCOTT 匹配 FORD。结果集里出现了 6 行数据，从技术上讲虽然没错，实际上却出现了重复数据。我们可以改用“小于”作为筛选条件以避免出现重复数据。（下面的查询语句去掉了 HIREDATE，这样查询结果会更接近最终结果集。）

select a.ename as emp1, b.ename as emp2
  from emp a, emp b
 where to_char(a.hiredate,'DMON') =
       to_char(b.hiredate,'DMON')
   and a.empno < b.empno
 order by 1
　
EMP1       EMP2
---------- ----------
JAMES      FORD
SCOTT      JAMES
SCOTT      FORD