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

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 dual

union all

select 'select z01_111,' from dual

union all

select distinct 'c' || z01_08 || decode ( z01_08 ,( select ma from mst ), '' , ',' ) from lst

union all

select 'from (select distinct z01_111 from z112  order by 1) a left join ' from dual

union 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 ,

c130

from ( 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 dual

union all

select 'select z01_08,' from dual

union all

select distinct 'c' || z01_111 || decode ( z01_111 ,( select ma from mst ), '' , ',' ) from lst

union all

select 'from (select distinct z01_08 from z112  order by 1) a left join ' from dual

union 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 ,

c2002

from ( 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                                1

130          1                                           1

110                     1

122                                           1



SQL >