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

最新推荐文章于 2024-06-13 11:24:43 发布

躺柒

最新推荐文章于 2024-06-13 11:24:43 发布

阅读量88

点赞数

分类专栏：选读SQL经典实例文章标签：笔记 sql 数据库

本文链接：https://blog.csdn.net/lyingSeven/article/details/131649548

版权

选读SQL经典实例专栏收录该内容

23 篇文章 1 订阅

订阅专栏

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

躺柒

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
选读SQL经典实例笔记04_日期运算（上）

3.2.1.2. SELECT列表里T500.ID减去了1，这是因为ID列的起始值是1，如果在JONES_HD基础上加上1就等同于从最终结果里排除掉了JONES_HD。2.1.1.2. X和Y都只有一条数据，因而即使没有连接条件也不会有问题，结果集最终只会有一行。3.2.1.1. WHERE子句的话，BLAKE_HD和JONES_HD相减后又加上了1。3.1.1. 计算出开始日期和结束日期之间相隔多少天（包含开始日期和结束日期）3.1.3. T500表的ID列每一个值都等于前面一行的值加上1。
复制链接

扫一扫