一个使用SQL语句解决的小问题

 

我们在实际中，会遇到各种各样的SQL编写需求。由于SQL语句的特点，一些看似在过程化语句中很困难的问题，借助一点点SQL技巧和经验，就可以获得意想不到的处理。

 

一个朋友在解决一个复杂问题时，将一个步骤问题咨询笔者。虽然在大牛们眼中不费吹灰之力，但是笔者觉得还是有必要写出来，作为将来遇到同类问题朋友们的一点点启发。

 

1、问题说明

 

原始问题比较复杂，这里只是提供一个简装本。注意：问题要求在10g上进行实现，这也就限制了一些Oracle特性，特别是分析函数的使用。

 

原始数据如下：

 

 

SQL> select * from v$version;

 

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE        10.2.0.1.0         Production

 

SQL> select * from t;

       ID1        ID2

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

         1          1

         2          2

         3          2

         4          2

         5          1

         6          2

         7          2

         8          2

 

8 rows selected

 

 

数据表列id1和id2，id1为sequence生成的主键列，显示结果按照id1进行排序。Id1取值单向增加，但是不一定连续。Id2为分组信息列，当按照id1进行排列的时候，取值为1的表示一组开始。顺序下取值为2的数据行归属在相同组，直到新一个取值1开始。

 

现在要求形成数据列，标记列名称，分别按照a，b，c…顺序下去。注意，每组数量不定。要求利用一句SQL解决，形成预期结果如下：

 

 

    

 ID1        ID2

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

         1          1       a

         2          2       a

         3          2       a

         4          2       a

         5          1       b

         6          2       b

         7          2       b

         8          2       b

 

 

2、问题分析

 

直观的感觉和我们常见的复杂SQL有点差别。之后的分组需求很类似于oracle的分组函数或者层次查询，但是又没有明显的特征和层级关系。而且组的关系是建立在id1序列和id2共同确定的。所以，直接使用分组函数和层级函数的想法可能有点问题。

 

第二个难点是关于分组的名称，abcd序列相差的关系，也就是组的差值1怎么体现？

 

首先，笔者希望将组的概念体现出来，也就是将id=1体现出来。笔者试着写一下。

 

 

SQL> select id1, id2, decode(id2,1,1,0) from t;

 

       ID1        ID2 DECODE(ID2,1,1,0)

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

         1          1                 1

         2          2                 0

         3          2                 0

         4          2                 0

         5          1                 1

         6          2                 0

         7          2                 0

         8          2                 0

 

8 rows selected

 

 

在第三列，每个组开头的数据行，都被设置为1，其余是0。之后，我们需要将不同的组分割开，此时，可以借助分析函数的累计和方法。对第三列进行累计处理，处理之后，可以方便的分出组来。

 

 

SQL> select id1, id2, sum(decode(id2,1,1,0)) over (order by id1) from t;

 

       ID1        ID2 SUM(DECODE(ID2,1,1,0))OVER(ORD

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

         1          1                              1

         2          2                              1

         3          2                              1

         4          2                              1

         5          1                              2

         6          2                              2

         7          2                              2

         8          2                              2

 

8 rows selected

 

 

Sum over是我们最常使用的分析函数。依次累计，就可以区分出数据组来。

 

最后一步就是将这些差值变为字符串a,b,c，可以使用chr函数。

 

 

SQL> select id1, id2, chr((sum(decode(id2,1,1,0)) over (order by id1))+96) res from t;

 

       ID1        ID2 RES

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

         1          1 a

         2          2 a

         3          2 a

         4          2 a

         5          1 b

         6          2 b

         7          2 b

         8          2 b

 

8 rows selected

 

 

当数据变化的时候，也是这样。

 

 

SQL> select id1, id2, chr((sum(decode(id2,1,1,0)) over (order by id1))+96) res from t;

 

       ID1        ID2 RES

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

         1          1 a

         2          2 a

         3          2 a

         4          2 a

         5          1 b

         6          2 b

         7          2 b

         8          2 b

        10          2 b

        11          1 c

        12          2 c

 

11 rows selected

 

 

3、结论

 

Oracle SQL配合提供的函数集合是非常强大的工具集合。我们只要充分利用这个利器，很多看似复杂和不可能实现的需求就可以方便的实现。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-733885/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17203031/viewspace-733885/