数据旋转及DB2递归的应用

前几天做一调查,需要下边这样的一个转换。

 

求一SQL--于数据转换

A中有以下数据

A1 1 01

A1 1 02

A1 1 03

A2 1 01

A2 2 01

A2 2 02

希望一SQL够查出下边这样的数据(暂时A中第三列只有01-03可能

A1 1 01-02-03

A2 1 01

A2 2 01-02

 

后来看过别人SQL后,我想出了这么一个笨招。

另外根据tanfufasql,我下午在回家的路上突然想到了一个方法,就是第三列几个字符串,但完全可以定种规则转成一些数字,譬如01成数字1,02成数字2,03成数字4,04话转成数字8,依此推。

然后group sum,根据sum就可以推知是由哪几个字符串值组成的了。

这种方法恐怕就是解析sum值这块需要花费过多的代了。另外不知道db2中有无将十制数字成二制的函数。

所以又了我昨的想法,已经测试成功了。放出其中个最重要的函数,主要是实现转换和解析个二

 

CREATE FUNCTION DB2ADMIN.DEC(VAL_SUM INTEGER)

RETURNS     VARCHAR(20)

    LANGUAGE SQL

    DETERMINISTIC

    NO EXTERNAL ACTION

    CONTAINS SQL

    INHERIT SPECIAL REGISTERS

   

 

   BEGIN ATOMIC

     DECLARE @PARA INT;

    DECLARE @DIV INT;

    DECLARE @MOD INT;

    DECLARE @BIN varchar(20);

    DECLARE @RET varchar(30);

    SET @BIN = '';

    SET @RET = '';

   

    SET @PARA = VAL_SUM;

   

    SET @DIV =@PARA/2;

    SET @MOD = MOD(@PARA,2);

   

    WHILE @PARA <> 0 DO

   

       SET @BIN = rtrim(ltrim(char(@MOD))) || rtrim(ltrim(@BIN));

       SET @PARA = @PARA/2;

       SET @DIV =@PARA/2;

       SET @MOD = MOD(@PARA,2);

    END WHILE;

 

   

    SET @BIN = REPEAT('0',10 - length(@BIN)) || @BIN;

    

    IF substr(@BIN,1,1) = '1' THEN

     SET  @RET = 'NW';

    END IF;

   

        IF substr(@BIN,2,1) = '1' THEN

     SET  @RET = @RET || '-' || 'RE';

    END IF;

   

        IF substr(@BIN,3,1) = '1' THEN

     SET  @RET = @RET || '-' || 'RB';

    END IF;

   

        IF substr(@BIN,4,1) = '1' THEN

    SET  @RET = @RET || '-' || 'RF';

    END IF;

   

        IF substr(@BIN,5,1) = '1' THEN

    SET  @RET = @RET || '-' || 'SV';

    END IF;

   

        IF substr(@BIN,6,1) = '1' THEN

     SET  @RET = @RET || '-' || 'NP';

    END IF;

   

        IF substr(@BIN,7,1) = '1' THEN

     SET  @RET = @RET || '-' || 'NC';

    END IF;

   

        IF substr(@BIN,8,1) = '1' THEN

     SET  @RET = @RET || '-' || 'CC';

    END IF;

   

        IF substr(@BIN,9,1) = '1' THEN

     SET  @RET = @RET || '-' || 'DC';

    END IF;

   

        IF substr(@BIN,10,1) = '1' THEN

     SET  @RET = @RET || '-' || 'AD';

    END IF;

   

    IF length(@RET) > 0 THEN

     IF substr(@RET,1,1) = '-' THEN

       SET @RET = substr(@RET,2);

     END IF;

    END IF;

 

    RETURN rtrim(ltrim(@RET));

  END

 

;

接着呢,我这块砖就引出了玉。呵呵。

 

db2递归的一个例子

myfriend2010

ORACLE中大家可能对递归(在oracle中很多人称作家族)已很熟悉,因ORACLE中的递归较简单,也容易掌握!接触DB2候,也碰到过递归问题,因研究的人少所以接触的候,db2有一神秘的感db2是很大的疑!就sql而言,凡是oraclesql实现的,db2肯定或多或少也可以实现,只是逻辑复杂点!因一直有人提问这边问题,所以今天抽时间写了一点

 

首先,db2递归的一点基

 

db2中的递归查询使用with实现,也称公共表达式,公共表达式在select句的始部分采用with子句的形式,在使用公共表达式的查询中可以多次使用它,并且公共表达式可以通名来接到他本身,这样就可以达到循的目的。

 

递归查询通常有3个部分需要定

 

一:一个公共表达式形式的虚表。

二:一个初始化表。

三:一个与虚行完全内接的助表。

 

需要使用UNION all合并上3查询,然后用select递归输出中得到最果。

 

大体上如下形式

with XX(x1,x2,x3) as  -------@0

(

select a.s,a.s1 from a  ----@1

union all  ----@2

select * from a,xx where a.s=xx.x1 ------@3

)

select ... from xx where .... -------@4

 

@0:with体,即虚

@1:初始化表,里需要定初始化的一些行,也就是你递归的出点,或者父行,部分逻辑行一次,它的果作递归的初始化内容。

@2:里必UNION all

@3:里需要定义递归的条件(助表),里定义递归逻辑,需要注意的是父行和子行接的逻辑一定要清楚父子系,不然很容易成死循的,里首先将初始化表的果作条件查询,在把行的果添加到虚表中,只要里能查询出来记录,那就会行下一步递归

@4:里就是表的查询语句。

 

递归更多的知,可以到google中找更专业料,里只我理解的一些西。

一个简单点的例子:

 

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

Q:求一SQL--于数据转换

 

 

A中有以下数据

A1 1 01

A1 1 02

A1 1 03

A2 1 01

A2 2 01

A2 2 02

希望一SQL够查出下边这样的数据(暂时A中第三列只有01-03可能

A1 1 01-02-03

A2 1 01

A2 2 01-02

 

A查询牵扯到全表的递归,用一般的sql句很搞定!

 

是我写的一个递归语句!分享大家看看!

 

为懒,所以把建立测试表的逻辑也略去了,里用with来生成上测试数据。

with t (t1,t2,t3) as (

values

('A1', 1 ,'01'),('A1' ,1, '02'),('A1', 1, '03'),

('A2', 1, '01'),('A2', 2, '01'),('A2', 2 ,'02')

)

select * from t;

测试一下:

 

C:\>db2 connect to dw

 

   数据库连接信息

 

数据         = DB2/NT 8.2.0

SQL 权标识         = DB2ADMIN

本地数据库别       = DW

 

 

C:\>db2 with t (t1,t2,t3) as ( \

db2 (cont.) => values \

db2 (cont.) => ('A1', 1 ,'01'),('A1' ,1, '02'),('A1', 1, '03'), \

db2 (cont.) => ('A2', 1, '01'),('A2', 2, '01'),('A2', 2 ,'02') \

db2 (cont.) => ) \

db2 (cont.) => select * from t

 

T1 T2          T3

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

A1           1 01

A1           1 02

A1           1 03

A2           1 01

A2           2 01

A2           2 02

 

  6 记录选择

 

 

在公布递归逻辑sql如下:

with t (t1,t2,t3) as (

values

('A1', 1 ,'01'),('A1' ,1, '02'),('A1', 1, '03'),

('A2', 1, '01'),('A2', 2, '01'),('A2', 2 ,'02')

),

t1(t11,t22,t33,t44,t55) as (

select t1,t2,t3,rownumber() over(partition by t1,t2),rownumber() over(partition by t1,t2) as t4 from t

),

t3(s1,s2,s3,s4,s5) as(

select t11,t22,cast(t33 as varchar(100)),t44,t55 from t1 where T44 =1 and t55=1

union all

select a.s1,a.s2,cast(a.s3||'-'||b.t33 as varchar(100)),a.s4+1,a.s5 from t3 a,t1 b

where a.s1=b.t11 and a.s4 = b.t55-1)

select s1,s2,s3 from t3 x where x.s4=(select max(s4) from t3 y where x.s1=y.s1) order by s1,s2

;

 

测试一下:

 

 

C:\>db2 connect to dw

 

   数据库连接信息

 

数据         = DB2/NT 8.2.0

SQL 权标识         = DB2ADMIN

本地数据库别       = DW

 

 

C:\>db2 with t (t1,t2,t3) as ( \

db2 (cont.) =>  values \

db2 (cont.) => ('A1', 1 ,'01'),('A1' ,1, '02'),('A1', 1, '03'), \

db2 (cont.) => ('A2', 1, '01'),('A2', 2, '01'),('A2', 2 ,'02') \

db2 (cont.) => ), \

db2 (cont.) =>  t1(t11,t22,t33,t44,t55) as ( \

db2 (cont.) =>  select t1,t2,t3,rownumber() over(partition by t1,t2),rownumber() over(partition by t1,t2) as t4 from t \

db2 (cont.) => ), \

db2 (cont.) => t3(s1,s2,s3,s4,s5) as( \

db2 (cont.) => select t11,t22,cast(t33 as varchar(100)),t44,t55 from t1 where T44 =1 and t55=1 \

db2 (cont.) =>  union all \

db2 (cont.) => select a.s1,a.s2,cast(a.s3||'-'||b.t33 as varchar(100)),a.s4+1,a.s5 from t3 a,t1 b \

db2 (cont.) =>  where a.s1=b.t11 and a.s4 = b.t55-1) \

db2 (cont.) => select s1,s2,s3 from t3 x where x.s4=(select max(s4) from t3 y where x.s1=y.s1) order by s1,s2

 

S1 S2          S3

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

SQL0347W  递归公共表表达式 "DB2ADMIN.T3" 可能包含无限循  SQLSTATE=01605

 

A1           1 01-02-03

A2           1 01-02

A2           2 01-02

 

  选择 3 记录,打印 1 条警告消息。

 

 

ok,搞定!

 

个人认为db2是很大的!大家一起学

 

的代逻辑可以了,需要改改

 

 

with t (t1,t2,t3) as (

values

('A1', 1 ,'01'),('A1' ,1, '02'),('A1', 1, '03'),

('A2', 1, '01'),('A2', 2, '01'),('A2', 2 ,'02')

),

t1(t11,t22,t33,t44,t55) as (

select t1,t2,t3,rownumber() over(partition by t1,t2),rownumber() over(partition by t1,t2) as t4 from t

),

t3(s1,s2,s3,s4,s5) as(

select t11,t22,cast(t33 as varchar(100)),t44,t55 from t1 where T44 =1 and t55=1

union all

select a.s1,a.s2,cast(a.s3||'-'||b.t33 as varchar(100)),a.s4+1,a.s5 from t3 a,t1 b where a.s1=b.t11 and a.s2=b.t22  and a.s4 = b.t55-1

)

select s1,s2,s3 from t3 x where x.s4=(select max(s4) from t3 y where x.s1=y.s1 and x.s2=y.s2) order by s1,s2

;

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

'A1'        1        '01-02-03'

'A2'        1        01

'A2'        2        '01-02'

 

这样!

posted @ 2008-03-18 09:13  彷徨......  阅读(874)  评论(1编辑  收藏  举报