單條SQL語句實現複雜邏輯的幾個例子(2)

查詢欄位a的值連續三條以上相同的記錄

初始表資料如下：

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操作：

首先肯定是拿上一條與下一條做對比，看看是否相同---&gtlead,lag分析函式可以實現這一點

計算相同數---&gtcount分析函式可以實現，但是這裡面有一個問題，分析函式雖然是逐條對比生成結果，但此處我們的依據是是否相同的欄位值，假設該欄位值為0或1的話，count() over(partition by )就沒有了依照，因此我們需要先將比較的結果欄位通過sum() over(order by rownum)計算相加，以便生成分割槽用的欄位。

如果計數>3則這些記錄符合我們的需求

OK，思路理清了，下面一步步來試試，首先生成比較是否相同的欄位：

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

*rownum列是為了排序用

然後生成用於partition的列

JSSWEB> select b.*, sum(na) over(order by rn) so

2 from (select a.*,

3 rownum rn,

4 decode(a, lag(a, 1, a) over(order by rownum), 0, 1) na

5 from tmp2 a) b

6 ;

A B C RN NA SO

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

1 2 3 1 0 0

1 4 5 2 0 0

1 3 6 3 0 0

2 3 3 4 1 1

1 5 7 5 1 2

2 5 8 6 1 3

1 6 9 7 1 4

1 2 3 8 0 4

1 4 5 9 0 4

1 3 6 10 0 4

10 rows selected

這下就清晰多了，剩下的就沒難度了，count() over()生成數量，取數量大於2的記錄即可：

JSSWEB> select a,b,c from(

2 select c.*, count(so) over(partition by so) ct

3 from (select b.*, sum(na) over(order by rn) so

4 from (select a.*,

5 rownum rn,

6 decode(a, lag(a, 1, a) over(order by rownum), 0, 1) na

7 from tmp2 a) b) c

8 )where ct>=3

9 ;

A B C

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

1 2 3

1 4 5

1 3 6

1 6 9

1 2 3

1 4 5

1 3 6

7 rows selected

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

檢視前例：

例1：按指定規則生成指定商品指定年限銷售額

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

單條SQL語句實現複雜邏輯的幾個例子(2)

相關文章