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

躺柒

墨衍会员 · AI 创作全网分发码龄4年

关注

原创最新推荐文章于 2026-06-12 12:20:46 发布 244 阅读 AI 写同款· GEO 优化 ›

SQL 经典实例立即下载

1. 两个日期之间相差的月份和年份

1.1. DB2

1.2. MySQL

1.3. sql

select mnth, mnth/12
    from (  select (year(max_hd) - year(min_hd))*12 +
         (month(max_hd) - month(min_hd)) as mnth
    from (
  select min(hiredate) as min_hd, max(hiredate) as max_hd
    from emp
         ) x
         ) y

1.4. Oracle

1.4.1. sql

select months_between(max_hd,min_hd),
         months_between(max_hd,min_hd)/12
    from (
  select min(hiredate) min_hd, max(hiredate) max_hd
    from emp
         ) x

1.5. PostgreSQL

1.5.1. sql

select mnth, mnth/12
     from (
   select ( extract(year from max_hd) -
            extract(year from min_hd) ) * 12
          +
          ( extract(month from max_hd) -
            extract(month from min_hd) ) as mnth
     from (
   select min(hiredate) as min_hd, max(hiredate) as max_hd
    from emp
         ) x
         ) y

1.6. SQL Server

1.6.1. sql

select datediff(month,min_hd,max_hd),
         datediff(month,min_hd,max_hd)/12
    from (
  select min(hiredate) min_hd, max(hiredate) max_hd
    from emp
         ) x

2. 两个日期之间相差的秒数、分钟数和小时数

2.1. 相差的天数分别乘以24（一天的小时数），1440（一天的分钟数）和86400（一天的秒数）

2.2. DB2

2.2.1. sql

select dy*24 hr, dy*24*60 min, dy*24*60*60 sec
     from (
   select ( days(max(case when ename = 'WARD'
                     then hiredate
                end)) -
            days(max(case when ename = 'ALLEN'
                     then hiredate
                end))
          ) as dy
    from emp
         ) x

2.3. Oracle

2.4. PostgreSQL

2.5. sql

select dy*24 as hr, dy*24*60 as min, dy*24*60*60 as sec
     from (
   select (max(case when ename = 'WARD'
                   then hiredate
               end) -
           max(case when ename = 'ALLEN'
                    then hiredate
               end)) as dy
      from emp
          ) x

2.6. MySQL

2.7. SQL Server

2.8. sql

select datediff(day,allen_hd,ward_hd)*24 hr,
          datediff(day,allen_hd,ward_hd)*24*60 min,
          datediff(day,allen_hd,ward_hd)*24*60*60 sec
     from (
   select max(case when ename = 'WARD'
                    then hiredate
              end) as ward_hd,
          max(case when ename = 'ALLEN'
                   then hiredate
             end) as allen_hd
    from emp
         ) x

3. 当前记录和下一条记录之间的日期差

3.1. DB2

3.1.1. sql

select x.*,
        days(x.next_hd) - days(x.hiredate) diff
   from (
 select e.deptno, e.ename, e.hiredate,
        (select min(d.hiredate) from emp d
          where d.hiredate ＞ e.hiredate) next_hd
   from emp e
  where e.deptno = 10
        ) x

3.2. Oracle

3.2.1. sql

select ename, hiredate, next_hd,
         next_hd - hiredate diff
    from (
  select deptno, ename, hiredate,
         lead(hiredate)over(order by hiredate) next_hd
    from emp
         )
   where deptno=10

3.3. PostgreSQL

3.3.1. sql

select x.*,
        x.next_hd - x.hiredate as diff
   from (
 select e.deptno, e.ename, e.hiredate,
        (select min(d.hiredate) from emp d
          where d.hiredate ＞ e.hiredate) as next_hd
   from emp e
  where e.deptno = 10
        ) x

3.4. MySQL

3.5. SQL Server

3.6. sql

select x.*,
        datediff(day,x.hiredate,x.next_hd) diff
   from (
 select e.deptno, e.ename, e.hiredate,
        (select min(d.hiredate) from emp d
          where d.hiredate ＞ e.hiredate) next_hd
   from emp e
  where e.deptno = 10
        ) x

3.6.2. datediff(x.next_hd, x.hiredate) diff

3.6.2.1. 对于MySQL 版本的DATEDIFF函数，需要省略第一个参数day，并把剩下的两个参数的顺序颠倒过来

4. 一年中有多少个星期一

4.1. 方案

4.1.1. 生成一年里所有可能的日期值

4.1.2. 格式化上述日期值，并找出它们分别是星期几

4.1.3. 统计每个“星期x”出现的次数

4.2. DB2

4.2.1. sql

with x (start_date,end_date)
   as (
   select start_date,
          start_date + 1 year end_date
     from (
   select (current_date -
           dayofyear(current_date) day)
           +1 day as start_date
     from t1
         )tmp
   union all
  select start_date + 1 day, end_date
    from x
   where start_date + 1 day ＜ end_date
  )
  select dayname(start_date),count(*)
    from x
   group by dayname(start_date)

4.3. Oracle

4.3.1. sql

with x as (
  select level lvl
    from dual
   connect by level ＜= (
     add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y')
   )
  )
  select to_char(trunc(sysdate,'y')+lvl-1,'DAY'), count(*)
    from x
  group by to_char(trunc(sysdate,'y')+lvl-1,'DAY')

4.3.2. sql

select to_char(trunc(sysdate,'y')+rownum-1,'DAY'),
        count(*)
   from t500
  where rownum ＜= (add_months(trunc(sysdate,'y'),12)
                   - trunc(sysdate,'y'))
  group by to_char(trunc(sysdate,'y')+rownum-1,'DAY')

4.3.2.1. Oracle早期版本

4.4. PostgreSQL

4.4.1. sql

select to_char(
             cast(
      date_trunc('year',current_date)
                  as date) + gs.id-1,'DAY'),
          count(*)
     from generate_series(1,366) gs(id)
    where gs.id ＜= (cast
                     ( date_trunc('year',current_date) +
                          interval '12 month' as date) -
  cast(date_trunc('year',current_date)
                        as date))
   group by to_char(
               cast(
         date_trunc('year',current_date)
            as date) + gs.id-1,'DAY')

4.5. MySQL

4.5.1. sql

select date_format(
             date_add(
                 cast(
               concat(year(current_date),'-01-01')
                      as date),
                      interval t500.id-1 day),
                      '%W') day,
          count(*)
     from t500
   where t500.id ＜= datediff(
                        cast(
                      concat(year(current_date)+1,'-01-01')
                             as date),
                        cast(
                      concat(year(current_date),'-01-01')
                             as date))
   group by date_format(
               date_add(
                   cast(
                 concat(year(current_date),'-01-01')
                        as date),
                        interval t500.id-1 day),
                        '%W')

4.6. SQL Server

4.6.1. sql

with x (start_date,end_date)
   as (
   select start_date,
          dateadd(year,1,start_date) end_date
     from (
   select cast(
          cast(year(getdate()) as varchar) + '-01-01'
               as datetime) start_date
     from t1
         ) tmp
  union all
  select dateadd(day,1,start_date), end_date
    from x
   where dateadd(day,1,start_date) ＜ end_date
  )
  select datename(dw,start_date),count(*)
    from x
   group by datename(dw,start_date)
 OPTION (MAXRECURSION 366)

收录于