Oracle日期操作技巧-优快云博客

12.确定季度的季度开始和结束日期的替代方法（不推荐）

-------------------------------------------------- --------------------------------------------

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 20071 yrq from dual union all
select 20072 yrq from dual union all
select 20073 yrq from dual union all
select 20074 yrq from dual
) x
) y

13.填写遗漏的日期

----------------------------------------------

with x
as (
select add_months(start_date,level-1) start_date
from (
select min(trunc(hiredate,'y')) start_date,
add_months(max(trunc(hiredate,'y')),12) end_date from emp) connect by level <= months_between(end_date,start_date))     
select x.start_date MTH, count(e.hiredate) num_hired from x, emp e where x.start_date = trunc(e.hiredate(+),'mm')
group by x.start_date
order by 1

14，替代方法

-----------------------------------

with x
as (
select add_months(start_date,level-1) start_date
from (
select min(trunc(hiredate,'y')) start_date,
add_months(max(trunc(hiredate,'y')),12) end_date
from emp)
connect by level <= months_between(end_date,start_date)
)
select x.start_date MTH, count(e.hiredate) num_hired
from x left join emp e
on (x.start_date = trunc(e.hiredate,'mm'))
group by x.start_date
order by 1

15.查找在2月或12月雇用的所有雇员，以及在星期二雇用的雇员。

-------------------------------------------------- ---------


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

16.使用日期的特定部分比较记录

-------------------------------------------------- --------------------------------

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

17.查找同一组或分区中的行之间的差异

-------------------------------------------------- -------------------------------------------------- -----

select deptno, ename, sal, hiredate,
lpad(nvl(to_char(sal-next_sal), 'N/A'), 10) diff from ( select deptno, ename, sal, hiredate,
lead(sal)over(partition by deptno
order by hiredate) next_sal from emp)

第18章找到一个连续值的范围

-------------------------------------------------- ----------------

select proj_id, proj_start, proj_end
from (
select proj_id, proj_start, proj_end,
lead(proj_start)over(order by proj_id) next_proj_start from job)
where next_proj_start = proj_end

第19章找到连续值范围的开始和结束

==================================

select proj_grp, min(proj_start), max(proj_end)
  from (
select proj_id,proj_start,proj_end,
sum(flag)over(order by proj_id) proj_grp
from ( select proj_id,proj_start,proj_end,
case when
lag(proj_end)over(order by proj_id) =proj_start
then 0 else 1 end flag from job
)) group by proj_grp

20，填充一系列值中的缺失值

-------------------------------------------------- ---------------------------

select x.yr, coalesce(cnt,0) cnt
        from (
      select extract(year from min(hiredate)over( )) -
             mod(extract(year from min(hiredate)over( )),10) +
             rownum-1 yr
        from emp
       where rownum <= 10
             ) x,
             (
     select to_number(to_char(hiredate,'YYYY')) yr, count(*) cnt
       from emp
      group by to_number(to_char(hiredate,'YYYY'))
            ) y
      where x.yr = y.yr(+)

第21章生成连续的数值

-------------------------------------------------- ----------------

with x
as (
select level id
from dual
connect by level <= 10
)
select * from x

22.印制卡兰德

-----------------------------------------

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