选读SQL经典实例笔记04_日期运算（上）
2023-07-11 07:13:08 来源：博客园
【资料图】
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 = 101.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 = 101.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=101.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=101.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=101.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 = 101.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-19812.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" ) y2.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" ) y2.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" ) y2.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-19813.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-19813.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-19813.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+13.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+13.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+13.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)+13.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标签：
