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

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

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

1.2. DB2数据库

1.2.1.   sql语句

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

复制代码

1.3. Oracle数据库

1.3.1.   sql语句

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

复制代码

1.4. PostgreSQL数据库

1.4.1.   sql语句

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

复制代码

1.5. MySQL数据库

1.5.1.   sql语句

1. select date_add(
2.            adddate(q_end,-day(q_end)+1),
3.                    interval -2 month) q_start,
4.           q_end
5.      from (
6.    select last_day(
7.        str_to_date(
8.            concat(
9.             substr(yrq,1,4),mod(yrq,10)*3),'%Y%m')) q_end
10.     from (
11.   select 20051 as yrq from t1 union all
12.   select 20052 as yrq from t1 union all
13.   select 20053 as yrq from t1 union all
14.   select 20054 as yrq from t1
15.          ) x
16.         ) y

复制代码

1.6. SQL Server数据库

1.6.1. sql语句

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

复制代码

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

2.1. DB2数据库

2.1.1.   sql语句

1. select quarter(dy-1 day) QTR,
2.          dy-3 month Q_start,
3.          dy-1 day Q_end
4.     from (
5.   select (current_date -
6.            (dayofyear(current_date)-1) day
7.              + (rn*3) month) dy
8.     from (
9.   select row_number()over() rn
10.     from emp
11.    fetch first 4 rows only
12.          ) x
13.          ) y

复制代码

2.2. Oracle数据库

2.2.1.  sql语句

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

复制代码

2.3. PostgreSQL数据库

2.3.1.   sql语句

1. select to_char(dy,'Q') as QTR,
2.          date(
3.            date_trunc('month',dy)-(2*interval '1 month')
4.          ) as Q_start,
5.          dy as Q_end
6.     from (
7.   select date(dy+((rn*3) * interval '1 month'))-1 as dy
8.     from (
9.   select rn, date(date_trunc('year',current_date)) as dy
10.     from generate_series(1,4) gs(rn)
11.          ) x
12.          ) y

复制代码

2.4. MySQL数据库

2.4.1.   sql语句

1. select quarter(adddate(dy,-1)) QTR,
2.          date_add(dy,interval -3 month) Q_start,
3.          adddate(dy,-1) Q_end
4.     from (
5.   select date_add(dy,interval (3*id) month) dy
6.     from (
7.   select id,
8.          adddate(current_date,-dayofyear(current_date)+1) dy
9.     from t500
10.    where id ＜= 4
11.          ) x
12.          ) y

复制代码

2.5. SQL Server数据库

2.5.1.   sql语句

1. with x (dy,cnt)
2.     as (
3. select dateadd(d,-(datepart(dy,getdate())-1),getdate()),
4.         1
5.    from t1
6.   union all
7. select dateadd(m,3,dy), cnt+1
8.    from x
9.   where cnt+1 ＜= 4
10. )
11. select datepart(q,dateadd(d,-1,dy)) QTR,
12.         dateadd(m,-3,dy) Q_start,
13.         dateadd(d,-1,dy) Q_end
14.    from x
15.   order by 1

复制代码

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

3.1. DB2数据库

3.2. MySQL数据库

3.3. SQL语句

1. select ename
2.    from emp
3.   where monthname(hiredate) in ('February','December')
4.      or dayname(hiredate) = 'Tuesday'

复制代码

3.4. Oracle数据库

3.5. PostgreSQL数据库

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

复制代码

3.7. SQL Server数据库

3.7.1.   sql语句

1. select ename
2.     from emp
3.    where datename(m,hiredate) in ('February','December')
4.       or datename(dw,hiredate) = 'Tuesday'

复制代码

4. 比较特定的日期要素

4.1. DB2数据库

4.1.1. sql语句

1. select a.ename ||
2.        ' was hired on the same month and weekday as '||
3.        b.ename msg
4.   from emp a, emp b
5. where (dayofweek(a.hiredate),monthname(a.hiredate)) =
6.        (dayofweek(b.hiredate),monthname(b.hiredate))
7.    and a.empno ＜ b.empno
8. order by a.ename

复制代码

4.2. Oracle数据库

4.3. PostgreSQL数据库

4.4. SQL语句

1. select a.ename ||
2.          ' was hired on the same month and weekday as '||
3.          b.ename as msg
4.     from emp a, emp b
5.    where to_char(a.hiredate,'DMON') =
6.          to_char(b.hiredate,'DMON')
7.      and a.empno ＜ b.empno
8.    order by a.ename

复制代码

4.5. MySQL数据库

4.5.1.  sql语句

1. select concat(a.ename,
2.         ' was hired on the same month and weekday as ',
3.         b.ename) msg
4.    from emp a, emp b
5.   where date_format(a.hiredate,'%w%M') =
6.         date_format(b.hiredate,'%w%M')
7.     and a.empno ＜ b.empno
8.   order by a.ename

复制代码

4.6. SQL Server数据库

4.6.1. sql语句

1. select a.ename +
2.        ' was hired on the same month and weekday as '+
3.        b.ename msg
4.   from emp a, emp b
5. where datename(dw,a.hiredate) = datename(dw,b.hiredate)
6.    and datename(m,a.hiredate)  = datename(m,b.hiredate)
7.    and a.empno ＜ b.empno
8. order by a.ename

复制代码

5. 识别重叠的日期区间

5.1. 基础数据

5.1.1. sql语句

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

复制代码

5.2. DB2数据库

5.3. Oracle数据库

5.4. PostgreSQL数据库

5.5. SQL语句

1. select a.empno,a.ename,
2.        'project '||b.proj_id||
3.         ' overlaps project '||a.proj_id as msg
4.   from emp_project a,
5.        emp_project b
6. where a.empno = b.empno
7.    and b.proj_start ＞= a.proj_start
8.    and b.proj_start ＜= a.proj_end
9.    and a.proj_id != b.proj_id

复制代码

5.6. MySQL数据库

5.6.1.   sql语句

1. select a.empno,a.ename,
2.          concat('project ',b.proj_id,
3.           ' overlaps project ',a.proj_id) as msg
4.     from emp_project a,
5.          emp_project b
6.    where a.empno = b.empno
7.      and b.proj_start ＞= a.proj_start
8.      and b.proj_start ＜= a.proj_end
9.      and a.proj_id != b.proj_id

复制代码

5.7. SQL Server数据库

5.7.1.  sql语句

1. select a.empno,a.ename,
2.         'project '+b.proj_id+
3.          ' overlaps project '+a.proj_id as msg
4.    from emp_project a,
5.         emp_project b
6.   where a.empno = b.empno
7.     and b.proj_start ＞= a.proj_start
8.     and b.proj_start ＜= a.proj_end
9.     and a.proj_id != b.proj_id

复制代码

6. 生成日历

6.1. DB2数据库

6.1.1.     sql语句

1. with x(dy,dm,mth,dw,wk)
2.       as (
3.   select (current_date -day(current_date) day +1 day) dy,
4.           day((current_date -day(current_date) day +1 day)) dm,
5.           month(current_date) mth,
6.           dayofweek(current_date -day(current_date) day +1 day) dw,
7.           week_iso(current_date -day(current_date) day +1 day) wk
8.     from t1
9.    union all
10.   select dy+1 day, day(dy+1 day), mth,
11.          dayofweek(dy+1 day), week_iso(dy+1 day)
12.     from x
13.    where month(dy+1 day) = mth
14.    )
15.   select max(case dw when 2 then dm end) as Mo,
16.          max(case dw when 3 then dm end) as Tu,
17.          max(case dw when 4 then dm end) as We,
18.          max(case dw when 5 then dm end) as Th,
19.          max(case dw when 6 then dm end) as Fr,
20.          max(case dw when 7 then dm end) as Sa,
21.          max(case dw when 1 then dm end) as Su
22.     from x
23.    group by wk
24.    order by wk

复制代码

6.2. Oracle数据库

6.2.1.   sql语句

1. with x
2.     as (
3.   select *
4.     from (
5.   select to_char(trunc(sysdate,'mm')+level-1,'iw') wk,
6.          to_char(trunc(sysdate,'mm')+level-1,'dd') dm,
7.          to_number(to_char(trunc(sysdate,'mm')+level-1,'d')) dw,
8.          to_char(trunc(sysdate,'mm')+level-1,'mm') curr_mth,
9.          to_char(sysdate,'mm') mth
10.     from dual
11.    connect by level ＜= 31
12.          )
13.    where curr_mth = mth
14.   )
15.   select max(case dw when 2 then dm end) Mo,
16.          max(case dw when 3 then dm end) Tu,
17.          max(case dw when 4 then dm end) We,
18.          max(case dw when 5 then dm end) Th,
19.          max(case dw when 6 then dm end) Fr,
20.          max(case dw when 7 then dm end) Sa,
21.          max(case dw when 1 then dm end) Su
22.     from x
23.    group by wk
24.    order by wk

复制代码

6.3. PostgreSQL数据库

6.3.1.  sql语句

1. select max(case dw when 2 then dm end) as Mo,
2.         max(case dw when 3 then dm end) as Tu,
3.         max(case dw when 4 then dm end) as We,
4.         max(case dw when 5 then dm end) as Th,
5.         max(case dw when 6 then dm end) as Fr,
6.         max(case dw when 7 then dm end) as Sa,
7.         max(case dw when 1 then dm end) as Su
8.    from (
9. select *
10.    from (
11. select cast(date_trunc('month',current_date) as date)+x.id,
12.         to_char(
13.            cast(
14.      date_trunc('month',current_date)
15.                 as date)+x.id,'iw') as wk,
16.         to_char(
17.            cast(
18.      date_trunc('month',current_date)
19.                 as date)+x.id,'dd') as dm,
20.          cast(
21.       to_char(
22.          cast(
23.    date_trunc('month',current_date)
24.                  as date)+x.id,'d') as integer) as dw,
25.          to_char(
26.             cast(
27.       date_trunc('month',current_date)
28.                  as date)+x.id,'mm') as curr_mth,
29.          to_char(current_date,'mm') as mth
30.    from generate_series (0,31) x(id)
31.         ) x
32.   where mth = curr_mth
33.         ) y
34.   group by wk
35.   order by wk

复制代码

6.4. MySQL数据库

6.4.1.   sql语句

1. select max(case dw when 2 then dm end) as Mo,
2.          max(case dw when 3 then dm end) as Tu,
3.          max(case dw when 4 then dm end) as We,
4.          max(case dw when 5 then dm end) as Th,
5.          max(case dw when 6 then dm end) as Fr,
6.          max(case dw when 7 then dm end) as Sa,
7.          max(case dw when 1 then dm end) as Su
8.     from (
9.   select date_format(dy,'%u') wk,
10.          date_format(dy,'%d') dm,
11.          date_format(dy,'%w')+1 dw
12.     from (
13.   select adddate(x.dy,t500.id-1) dy,
14.          x.mth
15.     from (
16.   select adddate(current_date,-dayofmonth(current_date)+1) dy,
17.          date_format(
18.              adddate(current_date,
19.                      -dayofmonth(current_date)+1),
20.                      '%m') mth
21.     from t1
22.          ) x,
23.            t500
24.    where t500.id ＜= 31
25.      and date_format(adddate(x.dy,t500.id-1),'%m') = x.mth
26.          ) y
27.          ) z
28.    group by wk
29.    order by wk

复制代码

6.5. SQL Server数据库

1.   with x(dy,dm,mth,dw,wk)
2.    as (
3.  select dy,
4.         day(dy) dm,
5.         datepart(m,dy) mth,
6.         datepart(dw,dy) dw,
7.         case when datepart(dw,dy) = 1
8.              then datepart(ww,dy)-1
9.              else datepart(ww,dy)
10.         end wk
11.    from (
12.  select dateadd(day,-day(getdate())+1,getdate()) dy
13.    from t1
14.         ) x
15.   union all
16.  select dateadd(d,1,dy), day(dateadd(d,1,dy)), mth,
17.         datepart(dw,dateadd(d,1,dy)),
18.         case when datepart(dw,dateadd(d,1,dy)) = 1
19.              then datepart(wk,dateadd(d,1,dy))-1
20.              else datepart(wk,dateadd(d,1,dy))
21.         end
22.    from x
23.   where datepart(m,dateadd(d,1,dy)) = mth
24.  )
25.  select max(case dw when 2 then dm end) as Mo,
26.         max(case dw when 3 then dm end) as Tu,
27.         max(case dw when 4 then dm end) as We,
28.         max(case dw when 5 then dm end) as Th,
29.         max(case dw when 6 then dm end) as Fr,
30.         max(case dw when 7 then dm end) as Sa,
31.         max(case dw when 1 then dm end) as Su
32.    from x
33.   group by wk
34.   order by wk

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！