高分求1个SQL语句~ 达人进!该怎么解决
高分求1个SQL语句~~~~ 达人进!
Table 定义如下
col1,col2
A, E1
A, E2
B, E1
A, E3
B, E2
希望取出来的结果
A,E1E2E3
B,E1E2
------解决方案--------------------
示例:
db2中关于递归(with)的使用
-------------------
1.创建测试表
create table zxt_test
( id varchar(10),
ivalue varchar(20),
iname varchar(20)
)
commit;
-----------
2.插入测试语句
insert into zxt_test values('1','aa','x'),('2','bb','x'),('3','bb','x'),('1','bb','y'),('2','bb','y'),('3','bb','y');
commit;
---------------
3.查看数据
select * from zxt_test;
1 'aa' 'x'
2 'bb' 'x'
3 'bb' 'x'
1 'bb' 'y'
2 'bb' 'y'
3 'bb' 'y'
----------------
4.with 的写法!
with
s as (
select row_number()over(partition by iname order by id) id1,
row_number()over(partition by iname order by id) id2,
ivalue,iname from zxt_test
)
,
t(iname,id1,id2,ivalue) as
(
select iname,id1,id2,cast(ivalue as varchar(100)) from s where id1 =1 and id2=1
union all
select t.iname,t.id1+1,t.id2,cast(s.ivalue||','||t.ivalue as varchar(100))
from s, t
where s.id2=t.id1+1 and t.iname = s.iname
)
select iname,ivalue from t where t.id1= (select max(id1) from s where s.iname = t.iname);
5.结果如下:
'x' 'bb,bb,aa'
'y' 'bb,bb,bb'
------解决方案--------------------
Table 定义如下
col1,col2
A, E1
A, E2
B, E1
A, E3
B, E2
希望取出来的结果
A,E1E2E3
B,E1E2
------解决方案--------------------
示例:
db2中关于递归(with)的使用
-------------------
1.创建测试表
create table zxt_test
( id varchar(10),
ivalue varchar(20),
iname varchar(20)
)
commit;
-----------
2.插入测试语句
insert into zxt_test values('1','aa','x'),('2','bb','x'),('3','bb','x'),('1','bb','y'),('2','bb','y'),('3','bb','y');
commit;
---------------
3.查看数据
select * from zxt_test;
1 'aa' 'x'
2 'bb' 'x'
3 'bb' 'x'
1 'bb' 'y'
2 'bb' 'y'
3 'bb' 'y'
----------------
4.with 的写法!
with
s as (
select row_number()over(partition by iname order by id) id1,
row_number()over(partition by iname order by id) id2,
ivalue,iname from zxt_test
)
,
t(iname,id1,id2,ivalue) as
(
select iname,id1,id2,cast(ivalue as varchar(100)) from s where id1 =1 and id2=1
union all
select t.iname,t.id1+1,t.id2,cast(s.ivalue||','||t.ivalue as varchar(100))
from s, t
where s.id2=t.id1+1 and t.iname = s.iname
)
select iname,ivalue from t where t.id1= (select max(id1) from s where s.iname = t.iname);
5.结果如下:
'x' 'bb,bb,aa'
'y' 'bb,bb,bb'
------解决方案--------------------
- SQL code
db2中的递归查询使用with来实现,也称为公共表达式,公共表达式在select语句的开始部分采用with子句 的形式,在使用公共表达式的查询中可以多次使用它,并且公共表达式还可以通过取别名来连接到他本 身,这样的话就可以达到循环的目的。 递归查询通常有个部分需要定义: 一:一个公共表达式形式的虚拟表。 二:一个初始化表。 三:一个与虚拟表进行完全内连接的辅助表。 需要使用UNION all合并上边个查询,然后用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中第三列只有-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) a s 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 b y 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,搞定!