选读SQL经典实例笔记04_日期运算（上）.png
1. 年月日加减法
1.1. DB2
1.1.1. sql
select hiredate -5 day as hd_minus_5D,
hiredate +5 day as hd_plus_5D,
hiredate -5 month as hd_minus_5M,
hiredate +5 month as hd_plus_5M,
hiredate -5 year as hd_minus_5Y,
hiredate +5 year as hd_plus_5Y
from emp
where deptno = 10
1.2. Oracle
1.2.1. sql
select hiredate-5 as hd_minus_5D,
hiredate+5 as hd_plus_5D,
add_months(hiredate,-5) as hd_minus_5M,
add_months(hiredate,5) as hd_plus_5M,
add_months(hiredate,-5*12) as hd_minus_5Y,
add_months(hiredate,5*12) as hd_plus_5Y
from emp
where deptno = 10
1.3. PostgreSQL
1.3.1. sql
select hiredate - interval '5 day' as hd_minus_5D,
hiredate + interval '5 day' as hd_plus_5D,
hiredate - interval '5 month' as hd_minus_5M,
hiredate + interval '5 month' as hd_plus_5M,
hiredate - interval '5 year' as hd_minus_5Y,
hiredate + interval '5 year' as hd_plus_5Y
from emp
where deptno=10
1.4. MySQL
1.4.1. sql
select hiredate - interval 5 day as hd_minus_5D,
hiredate + interval 5 day as hd_plus_5D,
hiredate - interval 5 month as hd_minus_5M,
hiredate + interval 5 month as hd_plus_5M,
hiredate - interval 5 year as hd_minus_5Y,
hiredate + interval 5 year as hd_plus_5Y
from emp
where deptno=10
1.4.2. sql
select date_add(hiredate,interval -5 day) as hd_minus_5D,
date_add(hiredate,interval 5 day) as hd_plus_5D,
date_add(hiredate,interval -5 month) as hd_minus_5M,
date_add(hiredate,interval 5 month) as hd_plus_5M,
date_add(hiredate,interval -5 year) as hd_minus_5Y,
date_add(hiredate,interval 5 year) as hd_plus_5DY
from emp
where deptno=10
1.5. SQL Server
1.5.1. sql
select dateadd(day,-5,hiredate) as hd_minus_5D,
dateadd(day,5,hiredate) as hd_plus_5D,
dateadd(month,-5,hiredate) as hd_minus_5M,
dateadd(month,5,hiredate) as hd_plus_5M,
dateadd(year,-5,hiredate) as hd_minus_5Y,
dateadd(year,5,hiredate) as hd_plus_5Y
from emp
where deptno = 10
1.6. SQL 的ISO 标准语法里规定了INTERVAL关键字以及紧随其后的字符串常量
1.6.1. 该标准要求INTERVAL值必须位于英文单引号内
1.6.2. PostgreSQL ( 和Oracle 9i数据库及其后续版本 ) 遵循了该标准
1.6.3. MySQL 则不支持英文单引号，略微偏离了标准
2. 两个日期之间的天数
2.1. 内嵌视图X和Y被用于分别获取WARD 和ALLEN 的HIREDATE
2.1.1. sql
select ward_hd, allen_hd
from (
select hiredate as ward_hd
from emp
where ename = 'WARD'
) y,
(
select hiredate as allen_hd
from emp
where ename = 'ALLEN'
) x
WARD_HD ALLEN_HD
----------- ---------
22-FEB-1981 20-FEB-1981
2.1.1.1. 因为X和Y之间没有任何连接条件，这里会产生笛卡儿积
2.1.1.2. X和Y都只有一条数据，因而即使没有连接条件也不会有问题，结果集最终只会有一行
2.2. DB2
2.2.1. sql
select days(ward_hd) - days(allen_hd)
from (
select hiredate as ward_hd
from emp
where ename = 'WARD'
) x,
(
select hiredate as allen_hd
from emp
where ename = 'ALLEN'
) y
2.3. Oracle
2.4. PostgreSQL
2.5. sql
select ward_hd - allen_hd
from (
select hiredate as ward_hd
from emp
where ename = 'WARD'
) x,
(
select hiredate as allen_hd
from emp
where ename = 'ALLEN'
) y
2.6. MySQL
2.7. SQL Server
2.8. sql
select datediff(day,allen_hd,ward_hd)
from (
select hiredate as ward_hd
from emp
where ename = 'WARD'
) x,
(
select hiredate as allen_hd
from emp
where ename = 'ALLEN'
) y
2.8.1.1. 对于MySQL 而言，只需去掉DATEDIFF函数的第一个参数，并翻转ALLEN_HD和WARD_HD的顺序即可
3. 两个日期之间的工作日天数
3.1. 思路
3.1.1. 计算出开始日期和结束日期之间相隔多少天（包含开始日期和结束日期）
3.1.2. 排除掉周末，统计有多少个工作日（实际是在计算有多少条记录）
3.1.2.1. sql
select case when ename = 'BLAKE'
then hiredate
end as blake_hd,
case when ename = 'JONES'
then hiredate
end as jones_hd
from emp
where ename in ( 'BLAKE','JONES' )
BLAKE_HD JONES_HD
----------- -----------
02-APR-1981
01-MAY-1981
3.1.2.2. sql
select max(case when ename = 'BLAKE'
then hiredate
end) as blake_hd,
max(case when ename = 'JONES'
then hiredate
end) as jones_hd
from emp
where ename in ( 'BLAKE','JONES' )
BLAKE_HD JONES_HD
----------- -----------
01-MAY-1981 02-APR-1981
3.1.2.2.1. 使用了聚合函数MAX，其目的在于排除掉Null
3.1.3. T500表的ID列每一个值都等于前面一行的值加上1
3.1.3.1. sql
select x.*, t500.*, jones_hd+t500.id-1
from (
select max(case when ename = 'BLAKE'
then hiredate
end) as blake_hd,
max(case when ename = 'JONES'
then hiredate
end) as jones_hd
from emp
where ename in ( 'BLAKE','JONES' )
) x,
t500
where t500.id ＜= blake_hd-jones_hd+1
BLAKE_HD JONES_HD ID JONES_HD+T5
----------- ----------- ---------- -----------
01-MAY-1981 02-APR-1981 1 02-APR-1981
01-MAY-1981 02-APR-1981 2 03-APR-1981
01-MAY-1981 02-APR-1981 3 04-APR-1981
01-MAY-1981 02-APR-1981 4 05-APR-1981
01-MAY-1981 02-APR-1981 5 06-APR-1981
01-MAY-1981 02-APR-1981 6 07-APR-1981
01-MAY-1981 02-APR-1981 7 08-APR-1981
01-MAY-1981 02-APR-1981 8 09-APR-1981
01-MAY-1981 02-APR-1981 9 10-APR-1981
01-MAY-1981 02-APR-1981 10 11-APR-1981
01-MAY-1981 02-APR-1981 11 12-APR-1981
01-MAY-1981 02-APR-1981 12 13-APR-1981
01-MAY-1981 02-APR-1981 13 14-APR-1981
01-MAY-1981 02-APR-1981 14 15-APR-1981
01-MAY-1981 02-APR-1981 15 16-APR-1981
01-MAY-1981 02-APR-1981 16 17-APR-1981
01-MAY-1981 02-APR-1981 17 18-APR-1981
01-MAY-1981 02-APR-1981 18 19-APR-1981
01-MAY-1981 02-APR-1981 19 20-APR-1981
01-MAY-1981 02-APR-1981 20 21-APR-1981
01-MAY-1981 02-APR-1981 21 22-APR-1981
01-MAY-1981 02-APR-1981 22 23-APR-1981
01-MAY-1981 02-APR-1981 23 24-APR-1981
01-MAY-1981 02-APR-1981 24 25-APR-1981
01-MAY-1981 02-APR-1981 25 26-APR-1981
01-MAY-1981 02-APR-1981 26 27-APR-1981
01-MAY-1981 02-APR-1981 27 28-APR-1981
01-MAY-1981 02-APR-1981 28 29-APR-1981
01-MAY-1981 02-APR-1981 29 30-APR-1981
01-MAY-1981 02-APR-1981 30 01-MAY-1981
3.1.3.1.1. Oracle语法
3.1.3.1.2. 一旦生成了所需数目的行记录，接着使用CASE表达式来标记每一个日期是工作日或者周末（若是工作日返回1，周末则返回0）
3.1.3.1.3. 使用聚合函数SUM来合计1的个数，并得到最终答案
3.2. DB2
3.2.1. sql
select sum(case when dayname(jones_hd+t500.id day -1 day)
in ( 'Saturday','Sunday' )
then 0 else 1
end) as days
from (
select max(case when ename = 'BLAKE'
then hiredate
end) as blake_hd,
max(case when ename = 'JONES'
then hiredate
end) as jones_hd
from emp
where ename in ( 'BLAKE','JONES' )
) x,
t500
where t500.id ＜= blake_hd-jones_hd+1
3.2.1.1. WHERE子句的话，BLAKE_HD和JONES_HD相减后又加上了1
3.2.1.2. SELECT列表里T500.ID减去了1，这是因为ID列的起始值是1，如果在JONES_HD基础上加上1就等同于从最终结果里排除掉了JONES_HD
3.3. Oracle
3.3.1. sql
select sum(case when to_char(jones_hd+t500.id-1,'DY')
in ( 'SAT','SUN' )
then 0 else 1
end) as days
from (
select max(case when ename = 'BLAKE'
then hiredate
end) as blake_hd,
max(case when ename = 'JONES'
then hiredate
end) as jones_hd
from emp
where ename in ( 'BLAKE','JONES' )
) x,
t500
where t500.id ＜= blake_hd-jones_hd+1
3.4. PostgreSQL
3.4.1. sql
select sum(case when trim(to_char(jones_hd+t500.id-1,'DAY'))
in ( 'SATURDAY','SUNDAY' )
then 0 else 1
end) as days
from (
select max(case when ename = 'BLAKE'
then hiredate
end) as blake_hd,
max(case when ename = 'JONES'
then hiredate
end) as jones_hd
from emp
where ename in ( 'BLAKE','JONES' )
) x,
t500
where t500.id ＜= blake_hd-jones_hd+1
3.5. MySQL
3.5.1. sql
select sum(case when date_format(
date_add(jones_hd,
interval t500.id-1 DAY),'%a')
in ( 'Sat','Sun' )
then 0 else 1
end) as days
from (
select max(case when ename = 'BLAKE'
then hiredate
end) as blake_hd,
max(case when ename = 'JONES'
then hiredate
end) as jones_hd
from emp
where ename in ( 'BLAKE','JONES' )
) x,
t500
where t500.id ＜= datediff(blake_hd,jones_hd)+1
3.6. SQL Server
3.6.1. sql
select sum(case when datename(dw,jones_hd+t500.id-1)
in ( 'SATURDAY','SUNDAY' )
then 0 else 1
end) as days
from (
select max(case when ename = 'BLAKE'
then hiredate
end) as blake_hd,
max(case when ename = 'JONES'
then hiredate
end) as jones_hd
from emp
where ename in ( 'BLAKE','JONES' )
) x,
t500
where t500.id ＜= datediff(day,jones_hd-blake_hd)+1
