Oracle SQL3-过程思维转换为集合思维

在写SQL文时，用集合思维写出的执行效果比过程思维写出的有时候要高效率一些。下面用一个简单的例子演示一下什么是过程思维和集合思维。

create table MYJOB
(
  EMPID      VARCHAR2(10),
  JOBNM      VARCHAR2(10),
  SRART_DATE DATE,
  END_DATE   DATE
);
commit;
insert into MYJOB (EMPID, JOBNM, SRART_DATE, END_DATE)
values ('001', 'AA', to_date('01-05-2017', 'dd-mm-yyyy'), to_date('01-06-2017', 'dd-mm-yyyy'));
insert into MYJOB (EMPID, JOBNM, SRART_DATE, END_DATE)
values ('001', 'BB', to_date('01-02-2017', 'dd-mm-yyyy'), to_date('01-04-2017', 'dd-mm-yyyy'));
insert into MYJOB (EMPID, JOBNM, SRART_DATE, END_DATE)
values ('001', 'CC', to_date('01-07-2016', 'dd-mm-yyyy'), to_date('01-09-2016', 'dd-mm-yyyy'));
insert into MYJOB (EMPID, JOBNM, SRART_DATE, END_DATE)
values ('002', 'AA', to_date('01-06-2017', 'dd-mm-yyyy'), to_date('01-07-2017', 'dd-mm-yyyy'));
insert into MYJOB (EMPID, JOBNM, SRART_DATE, END_DATE)
values ('002', 'BB', to_date('01-02-2017', 'dd-mm-yyyy'), to_date('01-05-2017', 'dd-mm-yyyy'));
insert into MYJOB (EMPID, JOBNM, SRART_DATE, END_DATE)
values ('001', 'AA', to_date('01-06-2017', 'dd-mm-yyyy'), to_date('01-07-2017', 'dd-mm-yyyy'));
commit;

数据脚本1

/*集合思维*/
select m.empid
  from (select m2.empid,
               m2.jobnm,
               sum(months_between(m2.srart_date, m2.end_date)) as sum_time
          from myjob m2
         group by m2.empid, m2.jobnm) m
 group by m.empid
having min(m.sum_time) = max(m.sum_time);

/*过程思维*/
with tmp_tbl as(
  select m.empid,
         m.jobnm,
         sum(months_between(m.srart_date, m.end_date)) as month_sum
    from myjob m
   group by m.empid, m.jobnm)
  select distinct m.empid
    from tmp_tbl m
   where not exists (select null
            from tmp_tbl m2
           where m.empid = m2.empid
             -- and m.jobnm = m2.jobnm
             and m.month_sum <> m2.month_sum)

 

转载于:https://www.cnblogs.com/aaron-song/p/7239435.html