oracle sum 空格处理,oracle中decode()\sum()\over()函数解决实际问题的应用

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

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

1、问题说明

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

原始数据如下：

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

CORE10.2.0.1.0Production

SQL> select * from t;

ID1ID2

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

11

22

32

42

51

62

72

82

8 rows selected

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

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

ID1ID2

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

11a

22a

32a

42a

51b

62b

72b

82b

2、问题分析

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

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

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

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

ID1ID2 DECODE(ID2,1,1,0)

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

111

220

320

420

511

620

720

820

8 rows selected

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

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

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

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

111

221

321

421

512

622

722

822

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;

ID1ID2 RES

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

11 a

22 a

32 a

42 a

51 b

62 b

72 b

82 b

8 rows selected

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

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

ID1ID2 RES

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

11 a

22 a

32 a

42 a

51 b

62 b

72 b

82 b

102 b

111 c

122 c

11 rows selected

3、结论

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