选读SQL经典实例笔记07_日期处理(下)

这篇具有很好参考价值的文章主要介绍了选读SQL经典实例笔记07_日期处理(下)。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

选读SQL经典实例笔记07_日期处理(下)文章来源地址https://www.toymoban.com/news/detail-558630.html

1. 一个季度的开始日期和结束日期

1.1. 以yyyyq格式(前面4位是年份,最后1位是季度序号)给出了年份和季度序号

1.2. DB2数据库

1.2.1.   sql语句

select (q_end-2 month) q_start,
          (q_end+1 month)-1 day q_end
     from (
   select date(substr(cast(yrq as char(4)),1,4) ||'-'||
          rtrim(cast(mod(yrq,10)*3 as char(2))) ||'-1') q_end
     from (
   select 20051 yrq from t1 union all
   select 20052 yrq from t1 union all
   select 20053 yrq from t1 union all
  select 20054 yrq from t1
         ) x
         ) y

1.3. Oracle数据库

1.3.1.   sql语句

select add_months(q_end,-2) q_start,
          last_day(q_end) q_end
     from (
   select to_date(substr(yrq,1,4)||mod(yrq,10)*3,'yyyymm') q_end
     from (
   select 20051 yrq from dual union all
   select 20052 yrq from dual union all
   select 20053 yrq from dual union all
   select 20054 yrq from dual
         ) x
         ) y

1.4. PostgreSQL数据库

1.4.1.   sql语句

select date(q_end-(2*interval '1 month')) as q_start,
          date(q_end+interval '1 month'-interval '1 day') as q_end
     from (
   select to_date(substr(yrq,1,4)||mod(yrq,10)*3,'yyyymm') as q_end
     from (
   select 20051 as yrq from t1 union all
   select 20052 as yrq from t1 union all
   select 20053 as yrq from t1 union all
   select 20054 as yrq from t1
         ) x
         ) y

1.5. MySQL数据库

1.5.1.   sql语句

select date_add(
           adddate(q_end,-day(q_end)+1),
                   interval -2 month) q_start,
          q_end
     from (
   select last_day(
       str_to_date(
           concat(
            substr(yrq,1,4),mod(yrq,10)*3),'%Y%m')) q_end
    from (
  select 20051 as yrq from t1 union all
  select 20052 as yrq from t1 union all
  select 20053 as yrq from t1 union all
  select 20054 as yrq from t1
         ) x
        ) y

1.6. SQL Server数据库

1.6.1. sql语句

select dateadd(m,-2,q_end) q_start,
         dateadd(d,-1,dateadd(m,1,q_end)) q_end
    from (
  select cast(substring(cast(yrq as varchar),1,4)+'-'+
         cast(yrq%10*3 as varchar)+'-1' as datetime) q_end
    from (
  select 20051 yrq from PRE_MID_DATA.dbo.para_country union all
  select 20052 yrq from PRE_MID_DATA.dbo.para_country union all
  select 20053 yrq from PRE_MID_DATA.dbo.para_country union all
  select 20054 yrq from PRE_MID_DATA.dbo.para_country
         ) x
         ) y

2. 每个季度的开始日期和结束日期

2.1. DB2数据库

2.1.1.   sql语句

select quarter(dy-1 day) QTR,
         dy-3 month Q_start,
         dy-1 day Q_end
    from (
  select (current_date -
           (dayofyear(current_date)-1) day
             + (rn*3) month) dy
    from (
  select row_number()over() rn
    from emp
   fetch first 4 rows only
         ) x
         ) y

2.2. Oracle数据库

2.2.1.  sql语句

select rownum qtr,
        add_months(trunc(sysdate,'y'),(rownum-1)*3) q_start,
        add_months(trunc(sysdate,'y'),rownum*3)-1 q_end
   from emp
  where rownum <= 4

2.3. PostgreSQL数据库

2.3.1.   sql语句

select to_char(dy,'Q') as QTR,
         date(
           date_trunc('month',dy)-(2*interval '1 month')
         ) as Q_start,
         dy as Q_end
    from (
  select date(dy+((rn*3) * interval '1 month'))-1 as dy
    from (
  select rn, date(date_trunc('year',current_date)) as dy
    from generate_series(1,4) gs(rn)
         ) x
         ) y

2.4. MySQL数据库

2.4.1.   sql语句

select quarter(adddate(dy,-1)) QTR,
         date_add(dy,interval -3 month) Q_start,
         adddate(dy,-1) Q_end
    from (
  select date_add(dy,interval (3*id) month) dy
    from (
  select id,
         adddate(current_date,-dayofyear(current_date)+1) dy
    from t500
   where id <= 4
         ) x
         ) y

2.5. SQL Server数据库

2.5.1.   sql语句

with x (dy,cnt)
    as (
 select dateadd(d,-(datepart(dy,getdate())-1),getdate()),
        1
   from t1
  union all
 select dateadd(m,3,dy), cnt+1
   from x
  where cnt+1 <= 4
 )
 select datepart(q,dateadd(d,-1,dy)) QTR,
        dateadd(m,-3,dy) Q_start,
        dateadd(d,-1,dy) Q_end
   from x
  order by 1

3. 依据特定时间单位检索数据

3.1. DB2数据库

3.2. MySQL数据库

3.3. SQL语句

select ename
   from emp
  where monthname(hiredate) in ('February','December')
     or dayname(hiredate) = 'Tuesday'

3.4. Oracle数据库

3.5. PostgreSQL数据库

select ename
   from emp
  where rtrim(to_char(hiredate,'month')) in ('february','december')
     or rtrim(to_char(hiredate,'day')) = 'tuesday'

3.7. SQL Server数据库

3.7.1.   sql语句

select ename
    from emp
   where datename(m,hiredate) in ('February','December')
      or datename(dw,hiredate) = 'Tuesday'

4. 比较特定的日期要素

4.1. DB2数据库

4.1.1. sql语句

select a.ename ||
       ' was hired on the same month and weekday as '||
       b.ename msg
  from emp a, emp b
 where (dayofweek(a.hiredate),monthname(a.hiredate)) =
       (dayofweek(b.hiredate),monthname(b.hiredate))
   and a.empno < b.empno
 order by a.ename

4.2. Oracle数据库

4.3. PostgreSQL数据库

4.4. SQL语句

select a.ename ||
         ' was hired on the same month and weekday as '||
         b.ename as msg
    from emp a, emp b
   where to_char(a.hiredate,'DMON') =
         to_char(b.hiredate,'DMON')
     and a.empno < b.empno
   order by a.ename

4.5. MySQL数据库

4.5.1.  sql语句

select concat(a.ename,
        ' was hired on the same month and weekday as ',
        b.ename) msg
   from emp a, emp b
  where date_format(a.hiredate,'%w%M') =
        date_format(b.hiredate,'%w%M')
    and a.empno < b.empno
  order by a.ename

4.6. SQL Server数据库

4.6.1. sql语句

select a.ename +
       ' was hired on the same month and weekday as '+
       b.ename msg
  from emp a, emp b
 where datename(dw,a.hiredate) = datename(dw,b.hiredate)
   and datename(m,a.hiredate)  = datename(m,b.hiredate)
   and a.empno < b.empno
 order by a.ename

5. 识别重叠的日期区间

5.1. 基础数据

5.1.1. sql语句

select *
  from emp_project
EMPNO ENAME      PROJ_ID PROJ_START  PROJ_END
----- ---------- ------- ----------- -----------
7782  CLARK            1 16-JUN-2005 18-JUN-2005
7782  CLARK            4 19-JUN-2005 24-JUN-2005
7782  CLARK            7 22-JUN-2005 25-JUN-2005
7782  CLARK           10 25-JUN-2005 28-JUN-2005
7782  CLARK           13 28-JUN-2005 02-JUL-2005
7839  KING             2 17-JUN-2005 21-JUN-2005
7839  KING             8 23-JUN-2005 25-JUN-2005
7839  KING            14 29-JUN-2005 30-JUN-2005
7839  KING            11 26-JUN-2005 27-JUN-2005
7839  KING             5 20-JUN-2005 24-JUN-2005
7934  MILLER           3 18-JUN-2005 22-JUN-2005
7934  MILLER          12 27-JUN-2005 28-JUN-2005
7934  MILLER          15 30-JUN-2005 03-JUL-2005
7934  MILLER           9 24-JUN-2005 27-JUN-2005
7934  MILLER           6 21-JUN-2005 23-JUN-2005

5.2. DB2数据库

5.3. Oracle数据库

5.4. PostgreSQL数据库

5.5. SQL语句

select a.empno,a.ename,
       'project '||b.proj_id||
        ' overlaps project '||a.proj_id as msg
  from emp_project a,
       emp_project b
 where a.empno = b.empno
   and b.proj_start >= a.proj_start
   and b.proj_start <= a.proj_end
   and a.proj_id != b.proj_id

5.6. MySQL数据库

5.6.1.   sql语句

select a.empno,a.ename,
         concat('project ',b.proj_id,
          ' overlaps project ',a.proj_id) as msg
    from emp_project a,
         emp_project b
   where a.empno = b.empno
     and b.proj_start >= a.proj_start
     and b.proj_start <= a.proj_end
     and a.proj_id != b.proj_id

5.7. SQL Server数据库

5.7.1.  sql语句

select a.empno,a.ename,
        'project '+b.proj_id+
         ' overlaps project '+a.proj_id as msg
   from emp_project a,
        emp_project b
  where a.empno = b.empno
    and b.proj_start >= a.proj_start
    and b.proj_start <= a.proj_end
    and a.proj_id != b.proj_id

6. 生成日历

6.1. DB2数据库

6.1.1.     sql语句

with x(dy,dm,mth,dw,wk)
      as (
  select (current_date -day(current_date) day +1 day) dy,
          day((current_date -day(current_date) day +1 day)) dm,
          month(current_date) mth,
          dayofweek(current_date -day(current_date) day +1 day) dw,
          week_iso(current_date -day(current_date) day +1 day) wk
    from t1
   union all
  select dy+1 day, day(dy+1 day), mth,
         dayofweek(dy+1 day), week_iso(dy+1 day)
    from x
   where month(dy+1 day) = mth
   )
  select max(case dw when 2 then dm end) as Mo,
         max(case dw when 3 then dm end) as Tu,
         max(case dw when 4 then dm end) as We,
         max(case dw when 5 then dm end) as Th,
         max(case dw when 6 then dm end) as Fr,
         max(case dw when 7 then dm end) as Sa,
         max(case dw when 1 then dm end) as Su
    from x
   group by wk
   order by wk

6.2. Oracle数据库

6.2.1.   sql语句

with x
    as (
  select *
    from (
  select to_char(trunc(sysdate,'mm')+level-1,'iw') wk,
         to_char(trunc(sysdate,'mm')+level-1,'dd') dm,
         to_number(to_char(trunc(sysdate,'mm')+level-1,'d')) dw,
         to_char(trunc(sysdate,'mm')+level-1,'mm') curr_mth,
         to_char(sysdate,'mm') mth
    from dual
   connect by level <= 31
         )
   where curr_mth = mth
  )
  select max(case dw when 2 then dm end) Mo,
         max(case dw when 3 then dm end) Tu,
         max(case dw when 4 then dm end) We,
         max(case dw when 5 then dm end) Th,
         max(case dw when 6 then dm end) Fr,
         max(case dw when 7 then dm end) Sa,
         max(case dw when 1 then dm end) Su
    from x
   group by wk
   order by wk

6.3. PostgreSQL数据库

6.3.1.  sql语句

select max(case dw when 2 then dm end) as Mo,
        max(case dw when 3 then dm end) as Tu,
        max(case dw when 4 then dm end) as We,
        max(case dw when 5 then dm end) as Th,
        max(case dw when 6 then dm end) as Fr,
        max(case dw when 7 then dm end) as Sa,
        max(case dw when 1 then dm end) as Su
   from (
 select *
   from (
 select cast(date_trunc('month',current_date) as date)+x.id,
        to_char(
           cast(
     date_trunc('month',current_date)
                as date)+x.id,'iw') as wk,
        to_char(
           cast(
     date_trunc('month',current_date)
                as date)+x.id,'dd') as dm,
         cast(
      to_char(
         cast(
   date_trunc('month',current_date)
                 as date)+x.id,'d') as integer) as dw,
         to_char(
            cast(
      date_trunc('month',current_date)
                 as date)+x.id,'mm') as curr_mth,
         to_char(current_date,'mm') as mth
   from generate_series (0,31) x(id)
        ) x
  where mth = curr_mth
        ) y
  group by wk
  order by wk

6.4. MySQL数据库

6.4.1.   sql语句

select max(case dw when 2 then dm end) as Mo,
         max(case dw when 3 then dm end) as Tu,
         max(case dw when 4 then dm end) as We,
         max(case dw when 5 then dm end) as Th,
         max(case dw when 6 then dm end) as Fr,
         max(case dw when 7 then dm end) as Sa,
         max(case dw when 1 then dm end) as Su
    from (
  select date_format(dy,'%u') wk,
         date_format(dy,'%d') dm,
         date_format(dy,'%w')+1 dw
    from (
  select adddate(x.dy,t500.id-1) dy,
         x.mth
    from (
  select adddate(current_date,-dayofmonth(current_date)+1) dy,
         date_format(
             adddate(current_date,
                     -dayofmonth(current_date)+1),
                     '%m') mth
    from t1
         ) x,
           t500
   where t500.id <= 31
     and date_format(adddate(x.dy,t500.id-1),'%m') = x.mth
         ) y
         ) z
   group by wk
   order by wk

6.5. SQL Server数据库

  with x(dy,dm,mth,dw,wk)
    as (
  select dy,
         day(dy) dm,
         datepart(m,dy) mth,
         datepart(dw,dy) dw,
         case when datepart(dw,dy) = 1
              then datepart(ww,dy)-1
              else datepart(ww,dy)
         end wk
    from (
  select dateadd(day,-day(getdate())+1,getdate()) dy
    from t1
         ) x
   union all
  select dateadd(d,1,dy), day(dateadd(d,1,dy)), mth,
         datepart(dw,dateadd(d,1,dy)),
         case when datepart(dw,dateadd(d,1,dy)) = 1
              then datepart(wk,dateadd(d,1,dy))-1
              else datepart(wk,dateadd(d,1,dy))
         end
    from x
   where datepart(m,dateadd(d,1,dy)) = mth
  )
  select max(case dw when 2 then dm end) as Mo,
         max(case dw when 3 then dm end) as Tu,
         max(case dw when 4 then dm end) as We,
         max(case dw when 5 then dm end) as Th,
         max(case dw when 6 then dm end) as Fr,
         max(case dw when 7 then dm end) as Sa,
         max(case dw when 1 then dm end) as Su
    from x
   group by wk
   order by wk

到了这里,关于选读SQL经典实例笔记07_日期处理(下)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包