單條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：按指定規則生成指定商品指定年限銷售額

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7607759/viewspace-239058/，如需轉載，請註明出處，否則將追究法律責任。