我们在实际中,会遇到各种各样的编写需求。由于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配合提供的函数集合是非常强大的工具集合。我们只要充分利用这个利器,很多看似复杂和不可能实现的需求就可以方便的实现。