单条SQL语句实现复杂逻辑的几个例子(3)

查询员工ID：1000的实际工作月数，注意过滤兼职月份

记录集如下：

ID STATION START_DATE END_DATE

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

1000 开发 2000-01-01 2000-04-01

1000 测试 2000-07-01 2000-10-01

1000 副经理 2001-01-01 2001-04-01

1000 DBA 2000-02-01 2000-03-01

1000 兼职经理 2000-03-01 2000-08-01

1000 经理 2001-05-01 2001-08-01

该员工的实际工作月份应为：15

建表语句如下：

create table tmp3 (id number,station varchar2(20),start_date date,end_date date);

insert into tmp3 (ID, STATION, START_DATE, END_DATE)

values ('1000', '开发', to_date('01-01-2000', 'dd-mm-yyyy'), to_date('01-04-2000', 'dd-mm-yyyy'));

insert into tmp3 (ID, STATION, START_DATE, END_DATE)

values ('1000', '测试', to_date('01-07-2000', 'dd-mm-yyyy'), to_date('01-10-2000', 'dd-mm-yyyy'));

insert into tmp3 (ID, STATION, START_DATE, END_DATE)

values ('1000', '副经理', to_date('01-01-2001', 'dd-mm-yyyy'), to_date('01-04-2001', 'dd-mm-yyyy'));

insert into tmp3 (ID, STATION, START_DATE, END_DATE)

values ('1000', 'DBA', to_date('01-02-2000', 'dd-mm-yyyy'), to_date('01-03-2000', 'dd-mm-yyyy'));

insert into tmp3 (ID, STATION, START_DATE, END_DATE)

values ('1000', '兼职经理', to_date('01-03-2000', 'dd-mm-yyyy'), to_date('01-08-2000', 'dd-mm-yyyy'));

insert into tmp3 (ID, STATION, START_DATE, END_DATE)

values ('1000', '经理', to_date('01-05-2001', 'dd-mm-yyyy'), to_date('01-08-2001', 'dd-mm-yyyy'));

commit;

解题思路：

这道题核心的问题在于在岗时间可能存在兼职的情况，因此要求实际工作月份的话就不能单纯sum(end_date-start_date)，如果说你一心想着比较各条记录的start_date,end_date，判断是否存在兼职月的话，黑黑，我不是说这样实现不了，只是。。。。太复杂了！！不妨换一种思路，我们只要遍历出它所有工作过的月份，然后count(distinct date)就是实际工作月份了。

要求出所有工作过的月份，就必须首先构造出足够数据的记录出来，这并不困难，熟悉connect by的朋友一定不陌生这种写法：select level from dual connect by rownum<=n;这里我们也借助这种方式来构造指定数据的记录集：

JSSWEB> select level - 1 lv

     2    from dual

     3  connect by rownum <=

     4             (select max(Months_between(end_date, start_date)) mb from test)

     5  ;

 

        LV

----------

         0

         1

         2

         3

         4

*Level为什么要-1呢，因为我们准备用add_months函数来生成日期，起始月数量当然得是0啊

然后与tmp3表做Cartesian，即能够生成比我们希望数量还要多的结果集(多不怕，别少就行啊)：

JSSWEB> select a.*,

     2         Months_between(end_date, start_date) mb,

     3         b.lv,

     4         add_months(a.start_date, lv) am

     5    from tmp3 a,

     6         (select level - 1 lv

     7            from dual

     8          connect by rownum <= (select max(Months_between(end_date, start_date)) mb

     9                                  from test t)) b

    10  ;

 

        ID STATION              START_DATE  END_DATE            MB         LV AM

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

      1000 开发                 2000-01-01  2000-04-01           3          0 2000-01-01

      1000 测试                 2000-07-01  2000-10-01           3          0 2000-07-01

      1000 副经理               2001-01-01  2001-04-01           3          0 2001-01-01

      1000 DBA                  2000-02-01  2000-03-01           1          0 2000-02-01

      1000 兼职经理             2000-03-01  2000-08-01           5          0 2000-03-01

      1000 经理                 2001-05-01  2001-08-01           3          0 2001-05-01

      1000 开发                 2000-01-01  2000-04-01           3          1 2000-02-01

      1000 测试                 2000-07-01  2000-10-01           3          1 2000-08-01

      1000 副经理               2001-01-01  2001-04-01           3          1 2001-02-01

      1000 DBA                  2000-02-01  2000-03-01           1          1 2000-03-01

      1000 兼职经理             2000-03-01  2000-08-01           5          1 2000-04-01

      1000 经理                 2001-05-01  2001-08-01           3          1 2001-06-01

      1000 开发                 2000-01-01  2000-04-01           3          2 2000-03-01

      1000 测试                 2000-07-01  2000-10-01           3          2 2000-09-01

      1000 副经理               2001-01-01  2001-04-01           3          2 2001-03-01

      1000 DBA                  2000-02-01  2000-03-01           1          2 2000-04-01

      1000 兼职经理             2000-03-01  2000-08-01           5          2 2000-05-01

      1000 经理                 2001-05-01  2001-08-01           3          2 2001-07-01

      1000 开发                 2000-01-01  2000-04-01           3          3 2000-04-01

      1000 测试                 2000-07-01  2000-10-01           3          3 2000-10-01

      1000 副经理               2001-01-01  2001-04-01           3          3 2001-04-01

      1000 DBA                  2000-02-01  2000-03-01           1          3 2000-05-01

      1000 兼职经理             2000-03-01  2000-08-01           5          3 2000-06-01

      1000 经理                 2001-05-01  2001-08-01           3          3 2001-08-01

      1000 开发                 2000-01-01  2000-04-01           3          4 2000-05-01

      1000 测试                 2000-07-01  2000-10-01           3          4 2000-11-01

      1000 副经理               2001-01-01  2001-04-01           3          4 2001-05-01

      1000 DBA                  2000-02-01  2000-03-01           1          4 2000-06-01

      1000 兼职经理             2000-03-01  2000-08-01           5          4 2000-07-01

      1000 经理                 2001-05-01  2001-08-01           3          4 2001-09-01

 

30 rows selected

剩下的工作就简单了，去除无效记录，再去重取数量即可：

JSSWEB> select count(unique am)

     2    from (select Months_between(end_date, start_date) mb,

     3                 b.lv,

     4                 add_months(a.start_date, lv) am

     5            from tmp3 a,

     6                 (select level - 1 lv

     7                    from dual

     8                  connect by rownum <=

     9                             (select max(Months_between(end_date, start_date)) mb

    10                                from test t)) b) c

    11   where c.mb > lv

    12   order by am;

 

COUNT(UNIQUEAM)

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

             15

得出结果15~

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

查看前两例：

例2：查询字段a的值连续三条以上相同的记录

例1：按指定规则生成指定商品指定年限销售额