利用oracle的with语句和动态sql,自动产生用交叉表格式显示的汇总语句

    技术2022-05-11  14

    SQL > select * from j1 ;    Z01_111 Z01 ---------- ---       1990 110      1991 120      1989 130      2001 130      2002 120      1998 122已选择6行。利用oracle的with语句和动态sql执行下面的 with lst as ( select distinct z01_08 from j1 order by z01_08 ), mst as ( select max ( z01_08 ) ma from j1 ) select 'with z112 as (select z01_111,z01_08, count(z01_111) s from j1 group by z01_08,z01_111) ' from dualunion allselect 'select z01_111,' from dual union all select distinct 'c' || z01_08 || decode ( z01_08 ,( select ma from mst ), '' , ',' ) from lstunion all select 'from (select distinct z01_111 from z112  order by 1) a left join ' from dualunion all select '(select z01_111,s c' || z01_08 || ' from z112 where z01_08=''' || z01_08 || ''') ' || 'a' || z01_08 || ' using (z01_111) ' || decode ( z01_08 ,( select ma from mst ), ';' , 'left join ' ) from lst ; 产生 'WITHZ112AS(SELECTZ01_111,Z01_08,COUNT(Z01_111)SFROMJ1GROUPBYZ01_08,Z01_111)' --------------------------------------------------------------------------------------- with z112 as ( select z01_111 , z01_08 , count ( z01_111 ) s from j1 group by z01_08 , z01_111 ) select z01_111 , c110 , c120 , c122 , c130from ( select distinct z01_111 from z112  order by 1 ) a left join ( select z01_111 , s c110 from z112 where z01_08 = '110' ) a110 using ( z01_111 ) left join ( select z01_111 , s c120 from z112 where z01_08 = '120' ) a120 using ( z01_111 ) left join ( select z01_111 , s c122 from z112 where z01_08 = '122' ) a122 using ( z01_111 ) left join ( select z01_111 , s c130 from z112 where z01_08 = '130' ) a130 using ( z01_111 ) ; 已选择11行。再执行产生的语句SQL > with z112 as ( select z01_111 , z01_08 , count ( z01_111 ) s from j1 group by z01_08 , z01_111 )   2  select z01_111 ,   3  c110 ,   4  c120 ,   5  c122 ,   6  c130  7  from ( select distinct z01_111 from z112  order by 1 ) a left join  8   ( select z01_111 , s c110 from z112 where z01_08 = '110' ) a110 using ( z01_111 ) left join  9   ( select z01_111 , s c120 from z112 where z01_08 = '120' ) a120 using ( z01_111 ) left join 10   ( select z01_111 , s c122 from z112 where z01_08 = '122' ) a122 using ( z01_111 ) left join 11   ( select z01_111 , s c130 from z112 where z01_08 = '130' ) a130 using ( z01_111 ) ;    Z01_111       C110       C120       C122       C130 ---------- ---------- ---------- ---------- ----------       1989                                           1      2001                                           1      1991                     1      1990          1      1998                                1      2002                     1已选择6行。SQL > create view tabu as with z112 as ( select z01_111 , z01_08 , count ( z01_111 ) s from j1 group by z01_08 , z01_111 )   2  select z01_111 ,   3  c110 ,   4  c120 ,   5  c122 ,   6  c130  7  from ( select distinct z01_111 from z112  order by 1 ) a left join  8   ( select z01_111 , s c110 from z112 where z01_08 = '110' ) a110 using ( z01_111 ) left join  9   ( select z01_111 , s c120 from z112 where z01_08 = '120' ) a120 using ( z01_111 ) left join 10   ( select z01_111 , s c122 from z112 where z01_08 = '122' ) a122 using ( z01_111 ) left join 11   ( select z01_111 , s c130 from z112 where z01_08 = '130' ) a130 using ( z01_111 ) ; 视图已建立。SQL > select * from tabu ;    Z01_111       C110       C120       C122       C130 ---------- ---------- ---------- ---------- ----------       1989                                           1      2001                                           1      1991                     1      1990          1      1998                                1      2002                     1已选择6行。如果要把交叉表的行列互换,只要交换语句中的z01_08和z01_111 with lst as ( select distinct z01_111 from j1 order by z01_111 ), mst as ( select max ( z01_111 ) ma from j1 ) select 'with z112 as (select z01_08,z01_111, count(z01_08) s from j1 group by z01_111,z01_08) ' from dualunion allselect 'select z01_08,' from dual union all select distinct 'c' || z01_111 || decode ( z01_111 ,( select ma from mst ), '' , ',' ) from lstunion all select 'from (select distinct z01_08 from z112  order by 1) a left join ' from dualunion all select '(select z01_08,s c' || z01_111 || ' from z112 where z01_111=''' || z01_111 || ''') ' || 'a' || z01_111 || ' using (z01_08) ' || decode ( z01_111 ,( select ma from mst ), ';' , 'left join ' ) from lst ; 执行后产生 'WITHZ112AS(SELECTZ01_08,Z01_111,COUNT(Z01_08)SFROMJ1GROUPBYZ01_111,Z01_08)' ------------------------------------------------------------------------------------------------------- with z112 as ( select z01_08 , z01_111 , count ( z01_08 ) s from j1 group by z01_111 , z01_08 ) select z01_08 , c1989 , c1990 , c1991 , c1998 , c2001 , c2002from ( select distinct z01_08 from z112  order by 1 ) a left join ( select z01_08 , s c1989 from z112 where z01_111 = '1989' ) a1989 using ( z01_08 ) left join ( select z01_08 , s c1990 from z112 where z01_111 = '1990' ) a1990 using ( z01_08 ) left join ( select z01_08 , s c1991 from z112 where z01_111 = '1991' ) a1991 using ( z01_08 ) left join ( select z01_08 , s c1998 from z112 where z01_111 = '1998' ) a1998 using ( z01_08 ) left join ( select z01_08 , s c2001 from z112 where z01_111 = '2001' ) a2001 using ( z01_08 ) left join ( select z01_08 , s c2002 from z112 where z01_111 = '2002' ) a2002 using ( z01_08 ) ; 已选择15行。SQL > with z112 as ( select z01_08 , z01_111 , count ( z01_08 ) s from j1 group by z01_111 , z01_08 )   2  select z01_08 ,   3  c1989 ,   4  c1990 ,   5  c1991 ,   6  c1998 ,   7  c2001 ,   8  c2002  9  from ( select distinct z01_08 from z112  order by 1 ) a left join 10   ( select z01_08 , s c1989 from z112 where z01_111 = '1989' ) a1989 using ( z01_08 ) left join 11   ( select z01_08 , s c1990 from z112 where z01_111 = '1990' ) a1990 using ( z01_08 ) left join 12   ( select z01_08 , s c1991 from z112 where z01_111 = '1991' ) a1991 using ( z01_08 ) left join 13   ( select z01_08 , s c1998 from z112 where z01_111 = '1998' ) a1998 using ( z01_08 ) left join 14   ( select z01_08 , s c2001 from z112 where z01_111 = '2001' ) a2001 using ( z01_08 ) left join 15   ( select z01_08 , s c2002 from z112 where z01_111 = '2002' ) a2002 using ( z01_08 ) ; Z01      C1989      C1990      C1991      C1998      C2001      C2002 --- ---------- ---------- ---------- ---------- ---------- ---------- 120                                1                                1130          1                                           1110                     1122                                           1SQL > 

    最新回复(0)