选读SQL经典实例笔记04_日期运算（上）

1. 年月日加减法

1.1. DB2

1.1.1.  sql

1. select hiredate -5 day   as hd_minus_5D,
2.         hiredate +5 day   as hd_plus_5D,
3.         hiredate -5 month as hd_minus_5M,
4.         hiredate +5 month as hd_plus_5M,
5.         hiredate -5 year  as hd_minus_5Y,
6.         hiredate +5 year  as hd_plus_5Y
7.    from emp
8.   where deptno = 10

复制代码

1.2. Oracle

1.2.1.  sql

1. select hiredate-5                 as hd_minus_5D,
2.         hiredate+5                 as hd_plus_5D,
3.         add_months(hiredate,-5)    as hd_minus_5M,
4.         add_months(hiredate,5)     as hd_plus_5M,
5.         add_months(hiredate,-5*12) as hd_minus_5Y,
6.         add_months(hiredate,5*12)  as hd_plus_5Y
7.    from emp
8.   where deptno = 10

复制代码

1.3. PostgreSQL

1.3.1.  sql

1. select hiredate - interval '5 day'   as hd_minus_5D,
2.         hiredate + interval '5 day'   as hd_plus_5D,
3.         hiredate - interval '5 month' as hd_minus_5M,
4.         hiredate + interval '5 month' as hd_plus_5M,
5.         hiredate - interval '5 year'  as hd_minus_5Y,
6.         hiredate + interval '5 year'  as hd_plus_5Y
7.    from emp
8.   where deptno=10

复制代码

1.4. MySQL

1.4.1.  sql

1. select hiredate - interval 5 day   as hd_minus_5D,
2.         hiredate + interval 5 day   as hd_plus_5D,
3.         hiredate - interval 5 month as hd_minus_5M,
4.         hiredate + interval 5 month as hd_plus_5M,
5.         hiredate - interval 5 year  as hd_minus_5Y,
6.         hiredate + interval 5 year  as hd_plus_5Y
7.    from emp
8.   where deptno=10

复制代码

1.4.2.  sql

1. select date_add(hiredate,interval -5 day)   as hd_minus_5D,
2.         date_add(hiredate,interval  5 day)   as hd_plus_5D,
3.         date_add(hiredate,interval -5 month) as hd_minus_5M,
4.         date_add(hiredate,interval  5 month) as hd_plus_5M,
5.         date_add(hiredate,interval -5 year)  as hd_minus_5Y,
6.         date_add(hiredate,interval  5 year)  as hd_plus_5DY
7.    from emp
8.   where deptno=10

复制代码

1.5. SQL Server

1.5.1.  sql

1. select dateadd(day,-5,hiredate)   as hd_minus_5D,
2.         dateadd(day,5,hiredate)    as hd_plus_5D,
3.         dateadd(month,-5,hiredate) as hd_minus_5M,
4.         dateadd(month,5,hiredate)  as hd_plus_5M,
5.         dateadd(year,-5,hiredate)  as hd_minus_5Y,
6.         dateadd(year,5,hiredate)   as hd_plus_5Y
7.    from emp
8.   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

1. select ward_hd, allen_hd
2.   from (
3. select hiredate as ward_hd
4.   from emp
5. where ename = 'WARD'
6.        ) y,
7.        (
8. select hiredate as allen_hd
9.   from emp
10. where ename = 'ALLEN'
11.        ) x
12. WARD_HD     ALLEN_HD
13. ----------- ---------
14. 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

1. select days(ward_hd) - days(allen_hd)
2.      from (
3.    select hiredate as ward_hd
4.      from emp
5.     where ename = 'WARD'
6.           ) x,
7.           (
8.    select hiredate as allen_hd
9.      from emp
10.    where ename = 'ALLEN'
11.          ) y

复制代码

2.3. Oracle

2.4. PostgreSQL

2.5. sql

1. select ward_hd - allen_hd
2.      from (
3.    select hiredate as ward_hd
4.      from emp
5.     where ename = 'WARD'
6.           ) x,
7.           (
8.    select hiredate as allen_hd
9.      from emp
10.    where ename = 'ALLEN'
11.          ) y

复制代码

2.6. MySQL

2.7. SQL Server

2.8. sql

1. select datediff(day,allen_hd,ward_hd)
2.      from (
3.    select hiredate as ward_hd
4.      from emp
5.     where ename = 'WARD'
6.           ) x,
7.           (
8.    select hiredate as allen_hd
9.      from emp
10.    where ename = 'ALLEN'
11.          ) 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

1. select case when ename = 'BLAKE'
2.             then hiredate
3.        end as blake_hd,
4.        case when ename = 'JONES'
5.             then hiredate
6.        end as jones_hd
7.   from emp
8. where ename in ( 'BLAKE','JONES' )
9. BLAKE_HD    JONES_HD
10. ----------- -----------
11.             02-APR-1981
12. 01-MAY-1981

复制代码

3.1.2.2. sql

1. select max(case when ename = 'BLAKE'
2.             then hiredate
3.        end) as blake_hd,
4.        max(case when ename = 'JONES'
5.             then hiredate
6.        end) as jones_hd
7.   from emp
8. where ename in ( 'BLAKE','JONES' )
9. BLAKE_HD    JONES_HD
10. ----------- -----------
11. 01-MAY-1981 02-APR-1981

复制代码

3.1.2.2.1. 使用了聚合函数MAX，其目的在于排除掉Null

3.1.3. T500表的ID列每一个值都等于前面一行的值加上1

3.1.3.1. sql

1. select x.*, t500.*, jones_hd+t500.id-1
2.   from (
3. select max(case when ename = 'BLAKE'
4.                 then hiredate
5.            end) as blake_hd,
6.        max(case when ename = 'JONES'
7.                 then hiredate
8.            end) as jones_hd
9.   from emp
10. where ename in ( 'BLAKE','JONES' )
11.        ) x,
12.        t500
13. where t500.id ＜= blake_hd-jones_hd+1
14. BLAKE_HD    JONES_HD            ID JONES_HD+T5
15. ----------- ----------- ---------- -----------
16. 01-MAY-1981 02-APR-1981          1 02-APR-1981
17. 01-MAY-1981 02-APR-1981          2 03-APR-1981
18. 01-MAY-1981 02-APR-1981          3 04-APR-1981
19. 01-MAY-1981 02-APR-1981          4 05-APR-1981
20. 01-MAY-1981 02-APR-1981          5 06-APR-1981
21. 01-MAY-1981 02-APR-1981          6 07-APR-1981
22. 01-MAY-1981 02-APR-1981          7 08-APR-1981
23. 01-MAY-1981 02-APR-1981          8 09-APR-1981
24. 01-MAY-1981 02-APR-1981          9 10-APR-1981
25. 01-MAY-1981 02-APR-1981         10 11-APR-1981
26. 01-MAY-1981 02-APR-1981         11 12-APR-1981
27. 01-MAY-1981 02-APR-1981         12 13-APR-1981
28. 01-MAY-1981 02-APR-1981         13 14-APR-1981
29. 01-MAY-1981 02-APR-1981         14 15-APR-1981
30. 01-MAY-1981 02-APR-1981         15 16-APR-1981
31. 01-MAY-1981 02-APR-1981         16 17-APR-1981
32. 01-MAY-1981 02-APR-1981         17 18-APR-1981
33. 01-MAY-1981 02-APR-1981         18 19-APR-1981
34. 01-MAY-1981 02-APR-1981         19 20-APR-1981
35. 01-MAY-1981 02-APR-1981         20 21-APR-1981
36. 01-MAY-1981 02-APR-1981         21 22-APR-1981
37. 01-MAY-1981 02-APR-1981         22 23-APR-1981
38. 01-MAY-1981 02-APR-1981         23 24-APR-1981
39. 01-MAY-1981 02-APR-1981         24 25-APR-1981
40. 01-MAY-1981 02-APR-1981         25 26-APR-1981
41. 01-MAY-1981 02-APR-1981         26 27-APR-1981
42. 01-MAY-1981 02-APR-1981         27 28-APR-1981
43. 01-MAY-1981 02-APR-1981         28 29-APR-1981
44. 01-MAY-1981 02-APR-1981         29 30-APR-1981
45. 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

1. select sum(case when dayname(jones_hd+t500.id day -1 day)
2.                     in ( 'Saturday','Sunday' )
3.                    then 0 else 1
4.               end) as days
5.      from (
6.    select max(case when ename = 'BLAKE'
7.                    then hiredate
8.               end) as blake_hd,
9.           max(case when ename = 'JONES'
10.                   then hiredate
11.              end) as jones_hd
12.     from emp
13.    where ename in ( 'BLAKE','JONES' )
14.           ) x,
15.           t500
16.    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

1. select sum(case when to_char(jones_hd+t500.id-1,'DY')
2.                      in ( 'SAT','SUN' )
3.                    then 0 else 1
4.               end) as days
5.      from (
6.    select max(case when ename = 'BLAKE'
7.                    then hiredate
8.               end) as blake_hd,
9.           max(case when ename = 'JONES'
10.                   then hiredate
11.              end) as jones_hd
12.     from emp
13.    where ename in ( 'BLAKE','JONES' )
14.          ) x,
15.          t500
16.    where t500.id ＜= blake_hd-jones_hd+1

复制代码

3.4. PostgreSQL

3.4.1.   sql

1. select sum(case when trim(to_char(jones_hd+t500.id-1,'DAY'))
2.                      in ( 'SATURDAY','SUNDAY' )
3.                    then 0 else 1
4.               end) as days
5.      from (
6.    select max(case when ename = 'BLAKE'
7.                    then hiredate
8.               end) as blake_hd,
9.           max(case when ename = 'JONES'
10.                   then hiredate
11.              end) as jones_hd
12.     from emp
13.    where ename in ( 'BLAKE','JONES' )
14.          ) x,
15.          t500
16.    where t500.id ＜= blake_hd-jones_hd+1

复制代码

3.5. MySQL

3.5.1.   sql

1. select sum(case when date_format(
2.                            date_add(jones_hd,
3.                                     interval t500.id-1 DAY),'%a')
4.                      in ( 'Sat','Sun' )
5.                    then 0 else 1
6.               end) as days
7.      from (
8.    select max(case when ename = 'BLAKE'
9.                    then hiredate
10.              end) as blake_hd,
11.           max(case when ename = 'JONES'
12.                    then hiredate
13.               end) as jones_hd
14.     from emp
15.    where ename in ( 'BLAKE','JONES' )
16.          ) x,
17.          t500
18.    where t500.id ＜= datediff(blake_hd,jones_hd)+1

复制代码

3.6. SQL Server

3.6.1.   sql

1. select sum(case when datename(dw,jones_hd+t500.id-1)
2.                      in ( 'SATURDAY','SUNDAY' )
3.                     then 0 else 1
4.               end) as days
5.      from (
6.    select max(case when ename = 'BLAKE'
7.                    then hiredate
8.               end) as blake_hd,
9.          max(case when ename = 'JONES'
10.                   then hiredate
11.              end) as jones_hd
12.     from emp
13.    where ename in ( 'BLAKE','JONES' )
14.          ) x,
15.          t500
16.    where t500.id ＜= datediff(day,jones_hd-blake_hd)+1

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！