单条SQL语句实现复杂逻辑的几个例子(5)

行列转换，将列中字符串以'/'分隔，转换成行

记录集如下：

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

通过实现如下结果集：

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

然后就是根据rn+'/'的位置来判断每一行应截取的字符串，比如rn为1的时候，就截取从0到第一个'/'的字符，rn为2时，就截取从第rn-1到第rn个'/'字符间的位置，特别需要注意的是，一定要判断好起始位置和结束位置，这里呢，我们就分成两步：

第一步decode(rn, 1, 1, instr(code, '/', 1, rn - 1) + length('/'))计算出截取开始位置，instr(code, '/', 1, rn)结束位置

第二步执行字符串截取：substr(code,st,decode(en,0,length(code),en-st))

JSSWEB> select distinct substr(code,st,decode(en,0,length(code),en-st)) code from(

     2  select a.*,

     3         decode(rn, 1, 1, instr(code, '/', 1, rn - 1) + length('/')) st,

     4         instr(code, '/', 1, rn) en

     5    from (select code, rn

     6            from tmp5,

     7                 (select rownum rn

     8                    from dual

     9                  connect by rownum <= (select max(length(code) -

    10                                                   length(replace(code, '/'))) + 1

    11                                          from tmp5))) a)c

    12  where instr(substr(code,st,decode(en,0,length(code),en-st)),'/')=0

    13  ;

 

CODE

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

c002

e

h

i

c0011

d

c029

g

c102

a

b

c007

c

c144

c001

c111

c167

c004

c112

c008

c003

c023

f  

23 rows selected

默认没有排序，当然，想要实现排序也非常简单，只要在适当位置引入rn即可轻松实现，怎么样，亲自动手去试试吧:)

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

查看前四例：

例4：将列值为0的列替换为距离它最近列的非0值

例3：查询员工ID：1000的实际工作月数

例2：查询字段a的值连续三条以上相同的记录

例1：按指定规则生成指定商品指定年限销售额