有哪些竞价网站,展馆展示设计公司哪家好,上海集酷网站,滨州网站建设报价前几天做一调查#xff0c;需要下边这样的一个转换。求一SQL--关于数据纵横转换的假设表A中有以下数据A1 1 01A1 1 02A1 1 03A2 1 01A2 2 01A2 2 02现希望一SQL能够查出下边这样的数据#xff08;暂时假设表A中第三列只有01-03这三种可能值#xff09;A1 1 01-02-03A2 1 01A…前几天做一调查需要下边这样的一个转换。 求一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后我想出了这么一个笨招。 另外根据tanfufa的sql我下午在回家的路上突然想到了一个方法就是第三列虽然为几个字符串但完全可以定义一种规则转成一些数字譬如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而言凡是oracle用sql能实现的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.sxx.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 t551 union all select a.s1,a.s2,cast(a.s3||-||b.t33 as varchar(100)),a.s41,a.s5 from t3 a,t1 b where a.s1b.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.s1y.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 t551 \ db2 (cont.) union all \ db2 (cont.) select a.s1,a.s2,cast(a.s3||-||b.t33 as varchar(100)),a.s41,a.s5 from t3 a,t1 b \ db2 (cont.) where a.s1b.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.s1y.s1) order by s1,s2 S1 S2 S3 -- ----------- ---------------------------------------------------------------------------------------------------- SQL0347W 递归公共表表达式 DB2ADMIN.T3 可能包含无限循环。 SQLSTATE01605 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 t551 union all select a.s1,a.s2,cast(a.s3||-||b.t33 as varchar(100)),a.s41,a.s5 from t3 a,t1 b where a.s1b.t11 and a.s2b.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.s1y.s1 and x.s2y.s2) order by s1,s2 ; ------------------------------------ A1 1 01-02-03 A2 1 01 A2 2 01-02 这样才对! 转载于:https://www.cnblogs.com/xioxu/archive/2008/03/18/1110796.html
