單條SQL語句實現複雜邏輯幾例~~
1、按指定規則生成指定商品2002年銷售額，其中彩電項值為2001年的銷售額加2000年的銷售額，微波爐為2000年銷售額, 然後彙總彩電+微波爐的2002年總銷售額。
2、查詢欄位a的值連續三條以上相同的記錄
3、查詢員工ID：1000的實際工作月數，注意過濾兼職月份
4、將列值為0的列替換為距離它最近列的非0值
5、行列轉換，將列中字串以'/'分隔，轉換成行
1、按指定規則生成指定商品2002年銷售額，其中彩電項值為2001年的銷售額加2000年的銷售額，微波爐為2000年銷售額, 然後彙總彩電+微波爐的2002年總銷售額。
表資料如下：
CITY PRODUCT YEAR SALES
------ -------------- -------- ---------
北京 彩電 1999 3000
北京 彩電 2000 2500
北京 彩電 2001 4500
北京 微波爐 1999 800
北京 微波爐 2000 7000
北京 微波爐 2001 333
北京 冰箱 1999 2323
北京 冰箱 2000 1212
北京 冰箱 2001 7676
天津 彩電 1999 212121
天津 彩電 2000 434343
天津 彩電 2001 564566
天津 微波爐 1999 23432
天津 微波爐 2000 232
天津 微波爐 2001 34234
天津 冰箱 1999 324324
天津 冰箱 2000 8987686
天津 冰箱 2001 768678
要求用SQL實現如下效果：
CITY PRODUCT YEAR SALES
------ -------------- -------- ---------
天津 微波爐 1999 23432
天津 微波爐 2000 232
天津 微波爐 2001 34234
天津 微波爐 2002 232
天津 冰箱 1999 324324
天津 冰箱 2000 8987686
天津 冰箱 2001 768678
天津 彩電 1999 212121
天津 彩電 2000 434343
天津 彩電 2001 564566
天津 彩電 2002 998909
天津 彩電+微波爐 2002 999141
北京 微波爐 1999 800
北京 微波爐 2000 7000
北京 微波爐 2001 333
北京 微波爐 2002 7000
北京 冰箱 1999 2323
北京 冰箱 2000 1212
北京 冰箱 2001 7676
北京 彩電 1999 3000
北京 彩電 2000 2500
北京 彩電 2001 4500
北京 彩電 2002 7000
北京 彩電+微波爐 2002 14000
建表語句如下：
create table tmp1(CITY varchar2(20), PRODUCT varchar2(20), YEAR number, SALES number);
insert into tmp1 values ('北京','彩電', 1999, 3000);
insert into tmp1 values ('北京','彩電', 2000, 2500);
insert into tmp1 values ('北京','彩電', 2001, 4500);
insert into tmp1 values ('北京','微波爐', 1999, 800);
insert into tmp1 values ('北京','微波爐', 2000, 7000);
insert into tmp1 values ('北京','微波爐', 2001, 333);
insert into tmp1 values ('北京','冰箱', 1999, 2323);
insert into tmp1 values ('北京','冰箱', 2000, 1212);
insert into tmp1 values ('北京','冰箱', 2001, 7676);
insert into tmp1 values ('天津','彩電', 1999, 212121);
insert into tmp1 values ('天津','彩電', 2000, 434343);
insert into tmp1 values ('天津','彩電', 2001, 564566);
insert into tmp1 values ('天津','微波爐', 1999, 23432);
insert into tmp1 values ('天津','微波爐', 2000, 232);
insert into tmp1 values ('天津','微波爐', 2001, 34234);
insert into tmp1 values ('天津','冰箱', 1999, 324324);
insert into tmp1 values ('天津','冰箱', 2000, 8987686);
insert into tmp1 values ('天津','冰箱', 2001, 768678);
解題思路：
本題初看起來一般都會下意識選擇透過group by rollup子句生成，但如果你選擇直接透過group by rollup的方式：
表資料如下：
CITY PRODUCT YEAR SALES
------ -------------- -------- ---------
北京 彩電 1999 3000
北京 彩電 2000 2500
北京 彩電 2001 4500
北京 微波爐 1999 800
北京 微波爐 2000 7000
北京 微波爐 2001 333
北京 冰箱 1999 2323
北京 冰箱 2000 1212
北京 冰箱 2001 7676
天津 彩電 1999 212121
天津 彩電 2000 434343
天津 彩電 2001 564566
天津 微波爐 1999 23432
天津 微波爐 2000 232
天津 微波爐 2001 34234
天津 冰箱 1999 324324
天津 冰箱 2000 8987686
天津 冰箱 2001 768678
要求用SQL實現如下效果：
CITY PRODUCT YEAR SALES
------ -------------- -------- ---------
天津 微波爐 1999 23432
天津 微波爐 2000 232
天津 微波爐 2001 34234
天津 微波爐 2002 232
天津 冰箱 1999 324324
天津 冰箱 2000 8987686
天津 冰箱 2001 768678
天津 彩電 1999 212121
天津 彩電 2000 434343
天津 彩電 2001 564566
天津 彩電 2002 998909
天津 彩電+微波爐 2002 999141
北京 微波爐 1999 800
北京 微波爐 2000 7000
北京 微波爐 2001 333
北京 微波爐 2002 7000
北京 冰箱 1999 2323
北京 冰箱 2000 1212
北京 冰箱 2001 7676
北京 彩電 1999 3000
北京 彩電 2000 2500
北京 彩電 2001 4500
北京 彩電 2002 7000
北京 彩電+微波爐 2002 14000
建表語句如下：
create table tmp1(CITY varchar2(20), PRODUCT varchar2(20), YEAR number, SALES number);
insert into tmp1 values ('北京','彩電', 1999, 3000);
insert into tmp1 values ('北京','彩電', 2000, 2500);
insert into tmp1 values ('北京','彩電', 2001, 4500);
insert into tmp1 values ('北京','微波爐', 1999, 800);
insert into tmp1 values ('北京','微波爐', 2000, 7000);
insert into tmp1 values ('北京','微波爐', 2001, 333);
insert into tmp1 values ('北京','冰箱', 1999, 2323);
insert into tmp1 values ('北京','冰箱', 2000, 1212);
insert into tmp1 values ('北京','冰箱', 2001, 7676);
insert into tmp1 values ('天津','彩電', 1999, 212121);
insert into tmp1 values ('天津','彩電', 2000, 434343);
insert into tmp1 values ('天津','彩電', 2001, 564566);
insert into tmp1 values ('天津','微波爐', 1999, 23432);
insert into tmp1 values ('天津','微波爐', 2000, 232);
insert into tmp1 values ('天津','微波爐', 2001, 34234);
insert into tmp1 values ('天津','冰箱', 1999, 324324);
insert into tmp1 values ('天津','冰箱', 2000, 8987686);
insert into tmp1 values ('天津','冰箱', 2001, 768678);
解題思路：
本題初看起來一般都會下意識選擇透過group by rollup子句生成，但如果你選擇直接透過group by rollup的方式：
2、查詢欄位a的值連續三條以上相同的記錄PHP code:
JSSWEB> select city, product, year, sum(sales)
2 from tmp1
3 group by city, rollup(product, year)
4 ;
CITY PRODUCT YEAR SUM(SALES)
-------------------- -------------------- ---------- ----------
北京 冰箱 2000 1212
北京 冰箱 1999 2323
北京 冰箱 2001 7676
北京 冰箱 11211
北京 彩電 2000 2500
北京 彩電 1999 3000
北京 彩電 2001 4500
北京 彩電 10000
北京 微波爐 2000 7000
北京 微波爐 1999 800
北京 微波爐 2001 333
北京 微波爐 8133
北京 29344
天津 冰箱 2000 8987686
天津 冰箱 1999 324324
天津 冰箱 2001 768678
天津 冰箱 10080688
天津 彩電 2000 434343
天津 彩電 1999 212121
天津 彩電 2001 564566
天津 彩電 1211030
天津 微波爐 2000 232
天津 微波爐 1999 23432
天津 微波爐 2001 34234
天津 微波爐 57898
天津 11349616
26 rows selected
--
初始表資料如下：
a b c
- - -
1 2 3
1 4 5
1 3 6
2 3 3
1 5 7
2 5 8
1 6 9
1 2 3
1 4 5
1 3 6
要求用SQL實現如下效果：
a b c
- - -
1 2 3
1 4 5
1 3 6
1 6 9
1 2 3
1 4 5
1 3 6
建表語句如下：
create table tmp2 (a number,b number, c number);
insert into tmp2 values (1,2,3);
insert into tmp2 values (1,4,5);
insert into tmp2 values (1,3,6);
insert into tmp2 values (2,3,3);
insert into tmp2 values (1,5,7);
insert into tmp2 values (2,5,8);
insert into tmp2 values (1,6,9);
insert into tmp2 values (1,2,3);
insert into tmp2 values (1,4,5);
insert into tmp2 values (1,3,6);
commit;
解題思路：
這道題看起來非常簡單，我們甚至一眼就能看出來哪些記錄是連線3條相同的，但千萬不要被其簡單的表象迷惑了，特別是那些下意識就能得出結論的問題，這往往會讓我們的思維陷入到自我的思維誤區中，而不再以計算機的執行模式去理解問題，因此這題核心要解決的問題就是將我們的思維方式轉換成sql可以理解的記數方式。
先來理一理我們的邏輯，看看能否轉換成對應的SQL操作：
首先肯定是拿上一條與下一條做對比，看看是否相同--->lead,lag分析函式可以實現這一點
計算相同數--->count分析函式可以實現，但是這裡面有一個問題，分析函式雖然是逐條對比生成結果，但此處我們的依據是是否相同的欄位值，假設該欄位值為0或1的話，count() over(partition by )就沒有了依照，因此我們需要先將比較的結果欄位透過sum() over(order by rownum)計算相加，以便生成分割槽用的欄位。
如果計數>3則這些記錄符合我們的需求
OK，思路理清了，下面一步步來試試，首先生成比較是否相同的欄位：
PHP code:
JSSWEB> select a.*,
2 rownum rn,
3 decode(a, lag(a, 1, a) over(order by rownum), 0, 1) na
4 from tmp2 a
5 ;
A B C RN NA
---------- ---------- ---------- ---------- ----------
1 2 3 1 0
1 4 5 2 0
1 3 6 3 0
2 3 3 4 1
1 5 7 5 1
2 5 8 6 1
1 6 9 7 1
1 2 3 8 0
1 4 5 9 0
1 3 6 10 0
10 rows selected
--
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
--
4、將列值為0的列替換為距離它最近列的非0值
記錄集如下：
ADDDATE ADDVALUE
-------------- ---------------
2007-03-01 0
2007-03-02 0
2007-03-05 3.64
2007-03-06 3.82
2007-03-07 0
2007-03-08 3.47
2007-03-09 0
2007-03-12 0
2007-03-13 4.01
2007-03-14 4.21
2007-03-15 4.12
2007-03-16 0
2007-03-17 0
用SQL實現如下效果：
ADDDATE ADDVALUE
-------------- ---------------
2007-03-01 3.64
2007-03-02 3.64
2007-03-05 3.64
2007-03-06 3.82
2007-03-07 3.47
2007-03-08 3.47
2007-03-09 4.01
2007-03-12 4.01
2007-03-13 4.01
2007-03-14 4.21
2007-03-15 4.12
2007-03-16 0
2007-03-17 0
建表語句如下：
create table tmp4 (adddate varchar2(20),addvalue number);
insert into tmp4 values ('2007-03-01',0);
insert into tmp4 values ('2007-03-02',0);
insert into tmp4 values ('2007-03-05',3.64);
insert into tmp4 values ('2007-03-06',3.82);
insert into tmp4 values ('2007-03-07',0);
insert into tmp4 values ('2007-03-08',3.47);
insert into tmp4 values ('2007-03-09',0);
insert into tmp4 values ('2007-03-12',0);
insert into tmp4 values ('2007-03-13',4.01);
insert into tmp4 values ('2007-03-14',4.21);
insert into tmp4 values ('2007-03-15',4.12);
insert into tmp4 values ('2007-03-16',0);
insert into tmp4 values ('2007-03-17',0);
Commit;
解題思路：
別想歪了，這道題用lead,lag之類分析函式是不行地，費事又不討好，最簡單的方式，如果不考慮執行效率的話，可以這樣：
JSSWEB> select a.adddate,
2 decode(a.addvalue,0, nvl((select b.addvalue
3 from tmp4 b
4 where b.adddate > a.adddate
5 and b.addvalue != 0
6 and rownum = 1),0),a.addvalue) addvalue
7 from tmp4 a
8 ;
ADDDATE ADDVALUE
-------------------- ----------
2007-03-01 3.64
2007-03-02 3.64
2007-03-05 3.64
2007-03-06 3.82
2007-03-07 3.47
2007-03-08 3.47
2007-03-09 4.01
2007-03-12 4.01
2007-03-13 4.01
2007-03-14 4.21
2007-03-15 4.12
2007-03-16 0
2007-03-17 0
--
記錄集如下：CODE
-------------------------------------
c001/c002/c007
c001/c003
c008/c0011/c029/c023
c004
c102/c111/c112/c144/c167
c008/c029/c023
c008
a/b/c/d/e/f/g/h/i
透過SQL實現如下結果集：
CODE
-------------------------------------
c002
e
h
i
c0011
d
c029
g
c102
a
b
c007
c
c144
c001
c111
c167
c004
c112
c008
c003
c023
f
建表語句如下：
create table tmp5 (code varchar2(50));
insert into tmp5 values ('c001/c002/c007');
insert into tmp5 values ('c001/c003');
insert into tmp5 values ('c008/c0011/c029/c023');
insert into tmp5 values ('c004');
insert into tmp5 values ('c102/c111/c112/c144/c167');
insert into tmp5 values ('c008/c029/c023');
insert into tmp5 values ('c008');
insert into tmp5 values ('a/b/c/d/e/f/g/h/i');
Commit;
解題思路：
行列轉換不少朋友都比較熟悉了，雖然說應用的範圍和機率非常低，但這確實是比較能夠考查sql理解能力的方式，這道題與普通行轉列的最大區別是轉換後的行數不固定，看起來有點麻煩，但是如果你深入理解了第3個示例，再回過頭來看這個，你一定會有種感覺：有點眉目了！
我們這裡也藉助第3例中所說的那種方式，先構造出一個足夠行數的結果集出來：
JSSWEB> select code,rn
2 from tmp5 a,
3 (select rownum rn
4 from dual
5 connect by rownum <=
6 (select max(length(code) - length(replace(code, '/'))) + 1
7 from tmp5))
8 ;
CODE RN
-------------------------------------------------- ----------
c001/c002/c007 1
c001/c003 1
c008/c0011/c029/c023 1
c004 1
c102/c111/c112/c144/c167 1
............
............
c008/c029/c023 9
c008 9
a/b/c/d/e/f/g/h/i 9
72 rows selected
--
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/82387/viewspace-1009790/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 單條SQL語句實現複雜邏輯的幾個例子(1)SQL
- 單條SQL語句實現複雜邏輯的幾個例子(2)SQL
- 單條SQL語句實現複雜邏輯的幾個例子(3)SQL
- 單條SQL語句實現複雜邏輯的幾個例子(4)SQL
- 單條SQL語句實現複雜邏輯的幾個例子(5)SQL
- 單條SQL語句實現複雜邏輯的幾個例子(4)續~SQL
- 簡單的幾條Insert語句引起的邏輯Standby應用延遲的診斷
- SQL Select語句邏輯執行順序SQL
- 詳解SQL中Groupings Sets 語句的功能和底層實現邏輯SQL
- 使用vue解決複雜邏輯Vue
- SVG 實現複雜線條動畫SVG動畫
- 如何將SQL寫成複雜邏輯 和構造資料SQL
- 一條簡單SQL語句的構成及語句解析SQL
- 實現四則運算的一條sql語句SQL
- 命令列寫複雜語句命令列
- 重構遺留程式碼（3）：複雜的條件語句
- 自定義註解例項實現SQL語句生成SQL
- 複雜查詢語句的使用
- 狀態機解決複雜邏輯及使用
- PL/SQL 條件控制語句SQL
- PL/SQL 動態sql語句例SQL
- C++ 條件與 If 語句：掌握邏輯判斷與流程控制精髓C++
- PLSQL Language Referenc-PL/SQL控制語句-條件選擇語句-簡單CASESQL
- Linux下邏輯測試語句引數和流程控制語句 if語句Linux
- 一條SQL語句的書寫SQL
- 一條很 巧妙的 SQL 語句SQL
- 一條sql語句的優化SQL優化
- 一條SQL語句的旅行之路SQL
- 如何應對報表開發中的複雜邏輯
- 如何用SQL語句實現以下功能.SQL
- sql語句實現表的行列倒置SQL
- 用if條件語句來實現瀏覽器相容簡單介紹瀏覽器
- 幾句程式碼簡單實現IoC容器
- 解析jwt實現邏輯JWT
- MySQL管理之SQL語句例項MySql
- SQL語句優化方法30例SQL優化
- java單例的幾種實現方法Java單例
- 邏輯式程式語言極簡實現（使用C#） - 1. 邏輯式程式語言介紹C#