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
最后,只要把上述查询结果连接起来变成符合要求的内容即可。