美文网首页
选读SQL经典实例笔记05_日期运算(下)

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

作者: 躺柒 | 来源:发表于2023-07-11 06:32 被阅读0次
选读SQL经典实例笔记05_日期运算(下).png

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)

相关文章

  • Oracle经典实例——日期运算

    1.年月日加减法 在给定日期的基础上加上或减去若干天、月或年,以入职时间hiredate为例: oracle执行日...

  • sql经典实例笔记

    1.1 检索所有行和列 1.2 筛选行 1.3 查找满足多个查询条件的行 1.4 筛选列 直接指定列即可。 1.5...

  • SQL Server行转列

    SQL行转列 经典实例 创建表格 SQL行转列 经典实例 创建表格 行转列 结果 参考链接

  • 03-SQL必备

    一、SQL SERVER 篇 Sql Server经典实例[https://www.cnblogs.com/jav...

  • 重温经典:宋代名篇选读396

    重温经典:宋代名篇选读397 重温经典:宋代名篇选读398 重温经典:宋代名篇选读399 重温经典:宋代名篇选读4...

  • 重温经典:宋代名篇选读351

    重温经典:宋代名篇选读352 重温经典:宋代名篇选读353 重温经典:宋代名篇选读354 重温经典:宋代名篇选读3...

  • 重温经典:宋代名篇选读366

    重温经典:宋代名篇选读367 重温经典:宋代名篇选读368 重温经典:宋代名篇选读369 重温经典:宋代名篇选读3...

  • 重温经典:宋代名篇选读361

    重温经典:宋代名篇选读362 重温经典:宋代名篇选读363 重温经典:宋代名篇选读364 重温经典:宋代名篇选读3...

  • 重温经典:宋代名篇选读371

    重温经典:宋代名篇选读372 重温经典:宋代名篇选读373 重温经典:宋代名篇选读374 重温经典:宋代名篇选读3...

  • 重温经典:宋代名篇选读356

    重温经典:宋代名篇选读357 重温经典:宋代名篇选读358 重温经典:宋代名篇选读359 重温经典:宋代名篇选读3...

网友评论

      本文标题:选读SQL经典实例笔记05_日期运算(下)

      本文链接:https://www.haomeiwen.com/subject/hqbeudtx.html