最新要闻

广告

手机

英国房地产因利率上升陷入困境 房价正以2011年来最快速度下跌

英国房地产因利率上升陷入困境 房价正以2011年来最快速度下跌

 宁夏评选出上半年10名“宁夏好人” 95后消防员因敬业奉献入选

宁夏评选出上半年10名“宁夏好人” 95后消防员因敬业奉献入选

家电

  1. 首页
  2. 移动通信
  3. 内容

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

来源：博客园


(相关资料图)

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"       ) xWARD_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-198101-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+1BLAKE_HD    JONES_HD            ID JONES_HD+T5----------- ----------- ---------- -----------01-MAY-1981 02-APR-1981          1 02-APR-198101-MAY-1981 02-APR-1981          2 03-APR-198101-MAY-1981 02-APR-1981          3 04-APR-198101-MAY-1981 02-APR-1981          4 05-APR-198101-MAY-1981 02-APR-1981          5 06-APR-198101-MAY-1981 02-APR-1981          6 07-APR-198101-MAY-1981 02-APR-1981          7 08-APR-198101-MAY-1981 02-APR-1981          8 09-APR-198101-MAY-1981 02-APR-1981          9 10-APR-198101-MAY-1981 02-APR-1981         10 11-APR-198101-MAY-1981 02-APR-1981         11 12-APR-198101-MAY-1981 02-APR-1981         12 13-APR-198101-MAY-1981 02-APR-1981         13 14-APR-198101-MAY-1981 02-APR-1981         14 15-APR-198101-MAY-1981 02-APR-1981         15 16-APR-198101-MAY-1981 02-APR-1981         16 17-APR-198101-MAY-1981 02-APR-1981         17 18-APR-198101-MAY-1981 02-APR-1981         18 19-APR-198101-MAY-1981 02-APR-1981         19 20-APR-198101-MAY-1981 02-APR-1981         20 21-APR-198101-MAY-1981 02-APR-1981         21 22-APR-198101-MAY-1981 02-APR-1981         22 23-APR-198101-MAY-1981 02-APR-1981         23 24-APR-198101-MAY-1981 02-APR-1981         24 25-APR-198101-MAY-1981 02-APR-1981         25 26-APR-198101-MAY-1981 02-APR-1981         26 27-APR-198101-MAY-1981 02-APR-1981         27 28-APR-198101-MAY-1981 02-APR-1981         28 29-APR-198101-MAY-1981 02-APR-1981         29 30-APR-198101-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

关键词：