單條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~
==================================
檢視前兩例：
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7607759/viewspace-239058/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 單條SQL語句實現複雜邏輯的幾個例子(1)SQL
- 單條SQL語句實現複雜邏輯的幾個例子(2)SQL
- 單條SQL語句實現複雜邏輯的幾個例子(4)SQL
- 單條SQL語句實現複雜邏輯的幾個例子(5)SQL
- 單條SQL語句實現複雜邏輯的幾個例子(4)續~SQL
- 單條SQL語句實現複雜邏輯幾例~~SQL
- 重構遺留程式碼（3）：複雜的條件語句
- 簡單的幾條Insert語句引起的邏輯Standby應用延遲的診斷
- 詳解SQL中Groupings Sets 語句的功能和底層實現邏輯SQL
- 複雜控制語句
- 實現四則運算的一條sql語句SQL
- SQL Select語句邏輯執行順序SQL
- 一條簡單SQL語句的構成及語句解析SQL
- 使用vue解決複雜邏輯Vue
- 一個複雜的json例子JSON
- SVG 實現複雜線條動畫SVG動畫
- Oracle sql with 語句語法與例子OracleSQL
- 如何將SQL寫成複雜邏輯 和構造資料SQL
- 複雜查詢語句的使用
- 幾個定位、查詢session的sql語句SessionSQL
- 命令列寫複雜語句命令列
- 常見邏輯語句逆向分析
- Oracle AWR中常用到的幾個SQL語句OracleSQL
- RxJava 系列番外篇：一個 RxJava 解決複雜業務邏輯的案例RxJava
- 單個SQL語句的10046 traceSQL
- 一條SQL語句的書寫SQL
- 一條很 巧妙的 SQL 語句SQL
- 一條sql語句的優化SQL優化
- 一條SQL語句的旅行之路SQL
- sql語句實現表的行列倒置SQL
- 狀態機解決複雜邏輯及使用
- 如何應對報表開發中的複雜邏輯
- PL/SQL 條件控制語句SQL
- WPF使用Shape實現複雜線條動畫動畫
- PLSQL Language Referenc-PL/SQL控制語句-條件選擇語句-簡單CASESQL
- C++ 條件與 If 語句：掌握邏輯判斷與流程控制精髓C++
- Linux下邏輯測試語句引數和流程控制語句 if語句Linux
- 一條sql語句的改進探索SQL