选读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模板网!

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

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

相关文章

  • 选读SQL经典实例笔记21_字符串处理

    2024年02月13日
    浏览(87)
  • 选读SQL经典实例笔记08_区间查询

    1.6.3.1. 即使同一天入职的员工不止一个人,也只会返回一个值 1.6.4.1. 使用MIN函数来确保只返回一个值 2.2.2.1. PROJ_START和PROJ_END的值决定哪些行属于同一个区间 2.2.2.2. 如果某一行的PROJ_START值等于上一行的PROJ_END值,那么该行就是“连续”的,或者说它属于某个组 3.4.1.1. ora

    2024年02月16日
    浏览(97)
  • 选读SQL经典实例笔记18_Exactly

    2024年02月14日
    浏览(72)
  • 选读SQL经典实例笔记16_逻辑否定

    2024年02月14日
    浏览(182)
  • 选读SQL经典实例笔记10_高级查询

    2024年02月17日
    浏览(96)
  • 选读SQL经典实例笔记14_层次查询

    2.6.1.1. sql 2.7.1.1. sql 3.5.1.1.  sql 4.5.1.1.  sql 5.5.1.1.  sql 6.7.1.1. Oracle Database 10g新增的CONNECT_BY_ROOT和CONNECT_BY_ISLEAF

    2024年02月15日
    浏览(104)
  • 选读SQL经典实例笔记15_窗口函数

    2.2.1.1. 分组不为空 2.2.1.1.1. 一个分组至少要拥有一个成员(行 2.2.1.1.2. 无法从一个空表中生成任何分组 2.2.1.2. 分组具有唯一性 2.2.1.2.1. 如果查询语句使用了GROUP BY子句,那么通常而言SELECT列表里就不再需要使用DISTINCT了 2.2.2.1. COUNT永远大于0 2.2.2.1.1. 无法从一个空表

    2024年02月14日
    浏览(39)
  • 选读SQL经典实例笔记01_检索和排序

    SMITH    800                           0 ALLEN   1600          300          1 WARD    1250         500          1 JONES   2975                          0 MARTIN  1250       1400         1 BLAKE   2850                         

    2024年02月11日
    浏览(116)
  • 选读SQL经典实例笔记22_2版增补

    4.1.3.1. 查找拼写不同但发音相同的字符串 6.1.1.1. 记录值的仪表存在误差 6.1.3.1. 意味着数据点是正确的,但应谨慎根据数据得出任何结论 9.1.2.1. 不使用CASE表达式或额外的连接操作

    2024年02月13日
    浏览(219)
  • 选读SQL经典实例笔记11_结果集变换

    3.6.1.1. 为了剔除掉Null,需要调用聚合函数MAX,并基于RN执行GROUP BY 3.9.1.1. 使用标量子查询基于EMPNO为每个员工排序 3.9.1.2. 针对标量子查询的返回值执行GROUP BY 3.9.1.3. 使用CASE表达式和聚合函数MAX实现结果集变换

    2024年02月16日
    浏览(80)

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

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

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

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

二维码1

领取红包

二维码2

领红包